Advanced Oracle PL/SQL Developer's Guide (Second Edition): Master the advanced concepts of PL/SQL for professional-level certification and learn the new capabilities of Oracle Database 12c, Second Edition

A well-written PL/SQL program should be able to answer the following fundamental questions:

Well, there are multiple tips and techniques to standardize PL/SQL coding practices. But before we drill down to the programming skills, let us familiarize ourselves with the structure of a PL/SQL program. A PL/SQL program can be broken down into four sections. Each section carries a specific objective and must exist in the same sequence in a program. Let us have a brief look at the sections:

The following block diagram shows the structure of a PL/SQL block:

A PL/SQL block is the elementary unit of a program that groups a set of procedural statements. Based on the sections included in a PL/SQL program unit, we can classify a program under following categories:

So, let's get started with our first anonymous PL/SQL block. The block declares a string and displays it on screen. Note that each line in the program ends with a semi-colon and the block ends with a slash (/) for code execution.

/*Enable the Serveroutput to display block messages*/
SET SERVEROUTPUT ON

/*Start the PL/SQL block*/
DECLARE
/*Declare a local variable and initialize with a default value*/
   L_STR VARCHAR2(50) := 'I am new to PL/SQL';
BEGIN
/*Print the result*/
   DBMS_OUTPUT.PUT_LINE('I Said - '||L_STR);
END;
/
I Said - I am new to PL/SQL

PL/SQL procedure successfully completed.

A cursor moves through the following stages during execution. Note that, in the case of an implicit cursor, all the steps are carried out by the Oracle Database. Let's take a quick look at the execution stages OPEN, FETCH, and CLOSE.

Cursor attributes hold the information about the cursor processing at each stage of its execution:

The following program uses the cursor attributes %ISOPEN, %NOTFOUND, and %ROWCOUNT to fetch the data from the EMP table and display it:

/*Enable the SERVEROUTPUT to display block messages*/
SET SERVEROUTPUT ON

/*Start the PL/SQL Block*/
DECLARE

/*Declare a cursor to select employees data*/
   CURSOR C_EMP IS
      SELECT EMPNO,ENAME
       FROM EMP;
   L_EMPNO EMP.EMPNO%TYPE;
   L_ENAME EMP.ENAME%TYPE;
BEGIN

/*Check if the cursor is already open*/
   IF NOT C_EMP%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE('***Displaying Employee Info***');
   END IF;

/*Open the cursor and iterate in a loop*/
   OPEN C_EMP;
   LOOP

/*Fetch the cursor data into local variables*/
   FETCH C_EMP INTO L_EMPNO, L_ENAME;
   EXIT WHEN C_EMP%NOTFOUND;

/*Display the employee information*/
      DBMS_OUTPUT.PUT_LINE(chr(10)||'Display Information for employee:'||C_EMP%ROWCOUNT);
      DBMS_OUTPUT.PUT_LINE('Employee Id:'||L_EMPNO);
      DBMS_OUTPUT.PUT_LINE('Employee Name:'||L_ENAME);
   END LOOP;
END;
/

***Displaying Employee Info***

Display Information for employee:1
Employee Id:7369
Employee Name:SMITH

Display Information for employee:2
Employee Id:7499
Employee Name:ALLEN

Display Information for employee:3
Employee Id:7521
Employee Name:WARD

Display Information for employee:4
Employee Id:7566
Employee Name:JONES
….
PL/SQL procedure successfully completed.

Looping through all the records of a cursor object can be facilitated with the use of the FOR loop. A FOR loop opening a cursor directly is known as a CURSOR FOR loop. The usage of the CURSOR FOR loop reduces the overhead of manually specifying the OPEN, FETCH, and CLOSE stages of a cursor.

The CURSOR FOR loop will best compact the code when working with multi-row explicit cursors. The following PL/SQL block demonstrates the purpose:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Start the PL/SQL block*/
DECLARE
/*Declare an explicit cursor to select employee information*/
   CURSOR CUR_EMP IS
      SELECT ename, sal
      FROM emp;
BEGIN
/*FOR Loop uses the cursor CUR_EMP directly*/
   FOR EMP IN CUR_EMP
   LOOP
/*Display message*/
   DBMS_OUTPUT.PUT_LINE(EMP.ename||' earns '||EMP.sal||' per month');
   END LOOP;
END;
/

SMITH earns 800 per month
ALLEN earns 1600 per month
WARD earns 1250 per month
JONES earns 2975 per month
MARTIN earns 1250 per month
BLAKE earns 2850 per month
CLARK earns 2450 per month
SCOTT earns 3000 per month
KING earns 5000 per month
TURNER earns 1500 per month
ADAMS earns 1100 per month
JAMES earns 950 per month
FORD earns 3000 per month
MILLER earns 1300 per month

PL/SQL procedure successfully completed.

Note that, with the CURSOR FOR loop, you do not need to declare the block variables to capture the cursor columns. The CURSOR FOR loop index implicitly acts as a record of the cursor type. Also, you do not need to explicitly open or close the cursor in the PL/SQL program.

As the name implies, system-defined exceptions are defined and maintained implicitly by the Oracle Database. They are defined in the Oracle STANDARD package. Whenever an exception occurs inside a program, the database picks up the appropriate exception from the available list. All system-defined exceptions are associated with a negative error code (except 1 to 100) and a short name, which is used while specifying the exception handlers.

For example, the following PL/SQL program includes a SELECT statement to select details of employee 8376. It raises NO_DATA_FOUND exception because employee id 8376 doesn't exist.

SET SERVEROUTPUT ON

/*Declare the PL/SQL block */
DECLARE
   L_ENAME VARCHAR2 (100);
   L_SAL NUMBER;
   L_EMPID NUMBER := 8376;
BEGIN

/*Write a SELECT statement */
   SELECT ENAME, SAL
   INTO L_ENAME, L_SAL
   FROM EMP
   WHERE EMPNO = L_EMPID;
END;
/

DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8

Let us rewrite the preceding PL/SQL block to include an EXCEPTION section and handle the NO_DATA_FOUND exception:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Start the PL/SQL block*/
DECLARE
   /*Declare the local variables*/
   L_ENAME VARCHAR2 (100);
   L_SAL NUMBER;
   L_EMPID NUMBER := 8376;
BEGIN
   /*SELECT statement to fetch the name and salary details of the employee*/
   SELECT ENAME, SAL
   INTO L_ENAME, L_SAL
   FROM EMP
   WHERE EMPNO = L_EMPID;
EXCEPTION
   /*Exception Handler */
   WHEN NO_DATA_FOUND THEN
   /*Display an informative message*/
   DBMS_OUTPUT.PUT_LINE ('No Employee exists with the id '||L_EMPID);
END;
/

No Employee exists with the id 8376

PL/SQL procedure successfully completed.

The following table lists some of the commonly used system-defined exceptions along with their short name and ORA error code:

Oracle allows users to create custom exceptions, specify names, associate error codes, and raise statements in line with the implementation logic. If PL/SQL applications are required to standardize the exception handling, not just to control the abnormal program flow but also to alter the program execution logic, you need to use user-defined exceptions. The user-defined exceptions are raised in the BEGIN..END section of the block using the RAISE statement.

There are three ways of declaring user-defined exceptions:

The following PL/SQL block declares a user-defined exception and raises it in the program body:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Declare a bind variable M_DIVISOR*/
VARIABLE M_DIVISOR NUMBER;

/*Declare a bind variable M_DIVIDEND*/
VARIABLE M_DIVIDEND NUMBER;

/*Assign value to M_DIVISOR as zero*/
EXEC :M_DIVISOR := 0;

PL/SQL procedure successfully completed.

/*Assign value to M_DIVIDEND as 10/
EXEC :M_DIVIDEND := 10;

PL/SQL procedure successfully completed.

/*Start the PL/SQL block*/
DECLARE
   /*Declare the local variables and initialize with the bind variables*/
   L_DIVISOR NUMBER := :M_DIVISOR;
   L_DIVIDEND NUMBER := :M_DIVIDEND;
   L_QUOT NUMBER;
   /*Declare an exception variable*/
   NOCASE EXCEPTION;
BEGIN
   /*Raise the exception if Divisor is equal to zero*/
   IF L_DIVISOR = 0 THEN
      RAISE NOCASE;
   END IF;
   L_QUOT := L_DIVIDEND/L_DIVISOR;
   DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT);
EXCEPTION
   /*Exception handler for NOCASE exception*/
   WHEN NOCASE THEN
      DBMS_OUTPUT.PUT_LINE('Divisor cannot be equal to zero');
END;
/
Divisor cannot be equal to zero

PL/SQL procedure successfully completed.

/*Assign a non zero value to M_DIVISOR*/
EXEC :M_DIVISOR := 2;

PL/SQL procedure successfully completed.

/*Re-execute the block */
SQL> /
The result : 5

PL/SQL procedure successfully completed.

RAISE_APPLICATION_ERROR (error_number, error_message[, {TRUE | FALSE}])

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Start the PL/SQL block */
DECLARE

/*Declare the birth date */
   L_DOB_MON DATE := '01-DEC-1981';

/*Declare a cursor to filter employees who were hired on birthday month*/
   CURSOR C IS
    SELECT empno, ename, hiredate
    FROM emp;
BEGIN
   FOR I IN C
   LOOP

   /*Raise exception, if birthdate is later than the hiredate */
      IF i.hiredate < l_dob_mon THEN
        RAISE_APPLICATION_ERROR (-20005,'Hiredate earlier than the given date!! Check for another employee');
      ELSE 
	DBMS_OUTPUT.PUT_LINE(i.ename||'was hired on'||i.hiredate);
      END IF;
   END LOOP;
END;
/

*
ERROR at line 1:
ORA-20005: Hiredate earlier than the given date!! Check for another employee
ORA-06512: at line 11

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Start the PL/SQL block */
DECLARE

/*Declare the birth date */
  L_DOB_MON DATE := '01-DEC-1981';

/*Declare the exception variable */
  INVALID_EMP_DATES EXCEPTION;
  PRAGMA EXCEPTION_INIT(INVALID_EMP_DATES,-20005);

/*Declare a cursor to filter employees who were hired on birthday month*/
   CURSOR C IS
    SELECT ename, deptno, hiredate
    FROM emp;
BEGIN
   FOR I IN C
   LOOP
      /*Raise exception, if birthdate is later than the hiredate */
      IF i.hiredate < l_dob_mon THEN
	   RAISE INVALID_EMP_DATES;
      ELSE 
	DBMS_OUTPUT.PUT_LINE(i.ename||'was hired on'||i.hiredate);
      END IF;
   END LOOP;
EXCEPTION
  WHEN INVALID_EMP_DATES THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM||'Hiredate earlier than the given date!! Check for another employee');
END;
/

ORA-20005: Hiredate earlier than the given date!! Check for another employee

PL/SQL procedure successfully completed.

Until now, we have seen that, as soon as the exception is raised in the procedural section of a PL/SQL block, the control jumps to the exception section and chooses the appropriate exception handler. The non-existence of the exception handler may lead to the abnormal termination of the program.

In the case of nested PL/SQL blocks, if the exception is raised in an inner block, the program control flows down to the exception section of the inner block. If the inner block handles the exception, it is executed and the program control returns to the next executable statement in the outer block.

If the inner block does not handle the exception, the program control continues to search for the appropriate handler and propagates to the exception section of the outer block. Yes, the execution of the outer block is skipped and the program control lands straight in to the exception section. The program control will continue to propagate the unhandled exception in the outer blocks until the appropriate one is found and handled.

For example, the following PL/SQL program contains a child block within the parent block:

/*Parent block*/
DECLARE
...
BEGIN
   /*Outer block executable statements*/
...
   /*Child Block*/
   DECLARE
  ...
   BEGIN
      ...
      /*Inner block executable statements*/
      ...
   EXCEPTION
      /*Inner block exception handlers*/
   END;
   ...
   /*Outer block executable statements*/
EXCEPTION
/*Outer block exception handlers*/
END;

If the exception is raised in one of the /*Inner block executable statements*/, the control flows to /*Inner block exception handlers*/. If the appropriate exception handler is not found, it propagates straight to the /*Outer block exception handlers*/ and execution of /*Outer block executable statements*/ is skipped.

When working with nested PL/SQL blocks, developers must be cautious while coding exception handling logic. The exception propagation should be thoroughly tested to build fail‑proof applications.

A procedure can either be executed from SQL*Plus or a PL/SQL block. The P_TO_UPPER procedure can be executed from SQL*Plus.

The following code shows the execution of the procedure from SQL*Plus (note that the parameter is passed using bind variable):

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Declare a session variable for the input*/
SQL> VARIABLE M_STR VARCHAR2(50);

/*Assign a test value to the session variable*/
SQL> EXECUTE :M_STR := 'My first PLSQL procedure';

PL/SQL procedure successfully completed.

/*Call the procedure P_TO_UPPER*/
SQL> EXECUTE P_TO_UPPER(:M_STR);
Input string in Upper case : MY FIRST PLSQL PROCEDURE

PL/SQL procedure successfully completed.

The P_TO_UPPER procedure can be called as a procedural statement within an anonymous PL/SQL block:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Start a PL/SQL block*/
SQL> BEGIN
      /*Call the P_TO_UPPER procedure*/
        P_TO_UPPER ('My first PLSQL procedure');
     END;
     /

Input string in Upper case : MY FIRST PLSQL PROCEDURE

PL/SQL procedure successfully completed.

Functions can either be called from a SQL*Plus environment or invoked from a PL/SQL program as a procedural statement.

The function F_GET_DOUBLE can be executed in the SQL* Plus command prompt as follows. As the function returns an output, you must declare a session variable and capture the function result in the variable.

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Declare a session variable M_NUM to hold the function output*/
VARIABLE M_NUM NUMBER;

/*Function is executed and output is assigned to the session variable*/
EXECUTE :M_NUM := F_GET_DOUBLE(10);

PL/SQL procedure successfully completed.

/*Print the session variable M_NUM*/
PRINT M_NUM

M_NUM
----------
20

The F_GET_DOUBLE function can be called from an anonymous block or a standalone subprogram.

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

DECLARE
      M_NUM NUMBER;
BEGIN
   M_NUM := F_GET_DOUBLE(10);
   DBMS_OUTPUT.PUT_LINE('Doubled the input value as : '||M_NUM);
END;
    /
Doubled the input value as : 20

PL/SQL procedure successfully completed.

Unlike procedures, a stored function can be called from a SELECT statement, provided it does not violate the database purity levels. The rules are as follows:

The F_GET_DOUBLE function can easily be embedded within a SELECT statement as it respects all the preceding rules:

/*Invoke the function F_GET_DOUBLE from SELECT statement*/
SQL> SELECT F_GET_DOUBLE(10) FROM DUAL;

F_GET_DOUBLE(10)
----------------
              20

The dependency matrix is automatically generated and maintained within the Oracle Database. The status of an object is the basis of dependency among the objects. The status of an object can be queried from the USER_OBJECTS (or ALL_OBJECTS or DBA_OBJECTS) dictionary view. The following query queries the status of the function F_GET_DOUBLE:

/*Check the status of the function F_GET_DOUBLE*/
SELECT status
FROM user_objects
WHERE object_name='F_GET_DOUBLE'
/

STATUS
-------
VALID

The system views DEPTREE and IDEPTREE capture the necessary information about the direct and indirect dependencies. Database administrators can create the views by running the script $ORACLE_HOME\RDBMS\ADMIN\utldtree.sql.

The execution steps for the script are as follows:

The script creates the DEPTREE_TEMPTAB table and the DEPTREE_FILL procedure. The DEPTREE_FILL procedure can be executed to populate the dependency details of an object.

/*Populate the dependency matrix for the function F_GET_DOUBLE*/
SQL> EXEC DEPTREE_FILL('FUNCTION','SCOTT','F_GET_DOUBLE');

PL/SQL procedure successfully completed.

Note that the first parameter of the DEPTREE_FILL procedure is the object type, the second is the owner, and the third is the object name.

The DEPTREE and IDEPTREE views can now be queried to view the dependency information.

Oracle provides the data dictionary views (USER_DEPENDENCIES, ALL_DEPENDENCIES, and DBA_DEPENDENCIES) to view the complete dependency metrics shared by an object. Besides the dependent object's list, it also lists its referencing object name and owner.

The following screenshot shows the structure of the dictionary view DBA_DEPENDENCIES:

In line with the conventional dependency phenomenon, the status validity of the dependent object depends upon the status of the referenced object. So, if the definition of the referenced object is altered, the dependent object is marked INVALID in the USER_OBJECTS view. Although object recompilation can easily solve the problem, the object invalidations may impact the application flow.

Oracle 11g introduced Fine Grained Dependency Tracking (FGD) to modify the dependency principle as follows. If the alteration in the referenced object does not affect the dependent object, the dependent object will remain in the VALID state. For instance, if a view is created with a fixed set of columns of a table and the table is altered to add a new column, the view will remain in a VALID state.

Oracle SQL Developer provides powerful features and interfaces for code development, administrative activities, and data modeling. It offers rich editors for developers who work with SQL, PL/SQL, and stored program units. SQL Developer can run SQL queries, monitor performance through execution plans and SQL tuning, and prepare scripts. Database developers can build PL/SQL applications, debug them, and perform run-time testing.

With SQL Developer 3.0, the DBA panel in the tool added the functionality to perform common DBA tasks and activities. The new feature additions such as the data miner, data modeler, database navigator, and DBMS scheduler provide a range of administrative functionalities in the tool. Database administrators can perform core administration tasks such as export/import through data pump, RMAN, user and role management, and resource management. SQL Developer integrates seamlessly with Oracle APEX, thereby allowing APEX developers to browse, deploy, and export applications.

With the most recent version, more DBA activities have been incorporated to make it more feature-rich and complete.

For database architects, SQL Developer offers a data modeling solution with SQL Developer Data Modeler (SDDM). The SDDM enables architects to create data flow diagrams, design versioning via subversion, import designer repositories, and most importantly perform logical, relational, and physical data modeling.

With the release of Oracle SQL Developer 4.0 and onwards, the tool follows Oracle's data management strategy by supporting Oracle Database 12c Multitenant option, cloud deployments, Oracle NoSQL and JSON. In addition, it includes a brand new command line interface utility to enhance user experience. With native support for Oracle Rest Data Services in Oracle Database 12c, Web application developers can work with SQL Developer to create and alter services. Database administrators can now run ASH, AWR, and ADDM reports from the performance page in the DBA panel. SQL Developer 4.0's new features can be summarized as follows:

Chapter 12, Working with Oracle SQL Developer, focuses on various features of the Oracle SQL Developer tool.