In this course you will learn about the fundamentals of Oracle PL/SQL programming language. You will gain a solid understanding of Pl/SQL datatypes and programming constructs like loops, conditional execution, cursors and exception handling, which will enable you to do effective debugging.
Course Introduction Hi. Welcome to Pluralsight. My name is Pankaj Jain, and welcome to this course, Oracle PL/SQL Fundamentals - Part 1. So what is PL/SQL? Oracle PL/SQL is Oracle's procedural extension to SQL. Oracle database allows us to create tables, objects, execute all the familiar SQL statements, like INSERT, UPDATE, and DELETE. In addition, it also offers PL/SQL a very powerful, and flexible procedural language, which gives us the ability to tie these SQL statements together. It gives us the ability to write procedural logic, to create loops, perform conditional executions, and provides us with a whole lot of other powerful constructs, which gives us a lot of fine grain control in executing these statements. It helps us define the business logic more effectively, and allows us to create reusable units of work, which I think is one of the greatest benefits of using it.
Anonymous Blocks Hi. Welcome to Pluralsight. My name is Pankaj Jain, and welcome to this module on Anonymous Blocks. Anonymous block is the most fundamental unit in PL/SQL, and having a solid understanding of the anonymous block will give you a good foundation for building your PL/SQL programming knowledge. In this module, we will talk about what anonymous blocks are, and where you can use them. How anonymous blocks are structured, and how you can put comments in them. We will see how and where you can declare variables in anonymous blocks. Scope and visibility are variables when we have nested blocks, and how to label blocks to resolve some of the visibility issues. All these are basic, and important concepts, and having a good handle of them will go a long way in your understanding, and usage of the PL/SQL programming language.
PL/SQL Commonly Used Datatypes Hi. Welcome to Pluralsight. My name is Pankaj Jain, and welcome to this module on PL/SQL Commonly Used Datatypes. Having a good understanding of some of the most commonly used PL/SQL datatypes will help you write PL/SQL code more easily, and efficiently. Oracle PL/SQL supports all the basic SQL types, as well as has a few specialized types of its own. It supports the scalar datatypes, which do not have internal components. We will be talking about datatypes, like numbers, characters, dates, etc. over here. Composite datatypes have internal components, which can be accessed using the dot notation. These include records and collections. We will talk about records in this module, and collections will be covered in a later course. Oracle also supports reference datatypes, like ref cursors, which we will talk about when we take a look at cursors later in this course. Oracle also supports a lot of other datatypes; for instance datatypes like CLOBs, BLOBs, BFILEs, which help store large objects like images, files, etc. We will not be covering those in this course. Oracle PL/SQL supports a lot of datatypes, and it is not possible to cover all of them in a single module. My aim is to introduce you to some of the commonly used datatypes, which I think you will be using most of the time.
Conditional Execution Hi, and welcome to Pluralsight. My name is Pankaj Jain. In this module, we will talk about Conditional Execution. I played a lot of chess growing up as a kid. For those of you who have played chess, you would agree that it is a fun game, but, at the same time, not the easiest as you have to think hard, strategize, evaluate several options before you make your next move. Evaluating options is not just in the game of chess. We do it in our day to day lives while making personal and business decisions. Oracle PL/SQL provides a very robust support for conditional evaluation in PL/SQL, and makes it simple for us by easy to understand constructs. Let us take a closer look. There are two kinds of conditional statements in Oracle. The IF statement. We have seen it a few times in the previous modules, though we did not talk much in detail then. The other type is the CASE statement and expressions, which can sometimes make the code more compact, and readable.
Debugging Hi. Welcome to Pluralsight. My name is Pankaj Jain, and welcome to this module on Debugging. Sometimes, when you find that the business logic in your application is not giving you the expected results, or that errors are being raised from your PL/SQL block, you will need to debug in order to find out what's going wrong. In case of errors, you might want to know, along with the error message, where exactly in your code the error occurred, as sometimes the code may span thousands of lines. Also, you might want to try what if situations to find how the logic will be processed, say with an alternate value. Oracle provides you with powerful debugging tools for PL/SQL. Oracle SQL Developer provides us with robust debugging capabilities with an interactive debugger. Let us take a look at this very important topic. Let us talk about some of the debugging options. Oracle provides us with a package DBMS_OUTPUT, which allows us to send debug messages from our anonymous blocks, and subprograms onto the client applications, or the Console. We have been seeing this package in use throughout our course, but we will talk more about it here, and point some of the settings and limitations. DBMS_UTILITY is again a built-in package from Oracle with several programs to help with debugging. We will look at the format errors stack function, which formats the error stack. We will also look at the format error back trace function, which helps in getting the line number where the error occurred. Lastly, we will look at SQL Developer Debugger, and see how we can debug a piece of PL/SQL code with it.