Oracle Advanced PL/SQL Developer Professional Guide

A PL/SQL block is the elementary unit of a program which groups a set of executable procedural statements. A block has defined "start" and "end" stages and it has three forms:

The skeleton of a PL/SQL block has four sections:

The following block diagram shows the structure of a PL/SQL block. The sections marked in red are the mandatory ones with the others being optional:

The PL/SQL following program illustrates the declaration and executable sections. The program declares a number variable, calculates its double value, and prints the result.

/*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_NUM NUMBER := 15;
   L_RES NUMBER;
BEGIN
/*Calculate the double of local variable*/
L_RES := L_NUM *2;
/*Print the result*/
   DBMS_OUTPUT.PUT_LINE('Double of '||TO_CHAR(L_NUM)||' is '||TO_CHAR(L_RES));
END;
/
Double of 15 is 30

PL/SQL procedure successfully completed.

SQL Developer is a Graphical User Interface (GUI) tool from the Oracle Corporation. It is free to use and includes a wide spectrum of new features with each of its releases. It allows the users to perform database activities such as SQL writing, PL/SQL execution, DBA activities easily, interactively, and considerably within time. Many of the database utilities such as unit testing, profiling, extended search, and SQL monitoring have been implemented as GUI utilities and can be easily used with the PL/SQL programs. The latest version of SQL Developer is 3.1 (3.1.07.42) which has been released on February 7, 2012. SQL Developer can be downloaded from the Oracle Technology Network link:

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

The latest release of the database development tool offers new features such as inclusion of RMAN under DBA navigator, support for data pump technology, renovated data copy and difference features, support for migration to Teradata and Sybase, and generation of PDF reports.

The key accomplishments offered by SQL Developer are:

The major offerings shown in the preceding list are by virtue of the tool features. The salient features of the SQL Developer tool are:

The following screenshot shows the Start Page of Oracle SQL Developer:

SQL Developer—the history

The following flowchart demonstrates the release history of SQL Developer:

SQL Worksheet

The SQL Worksheet window is the primary editor to perform database activities. It is used to write and execute SQL statements, PL/SQL code, and SQL*Plus commands.

A new worksheet can be opened in two ways:

• Hitting the shortcut key, Alt + F10

• Navigating to Tools | SQL Worksheet

When a SQL worksheet is opened by following either of the preceding options, a window pops up which prompts the user to select the database connection applicable for the current worksheet. The available database connection to open a new SQL worksheet can be selected from the drop-down option:

The worksheet contains multiple, quick utility actions as iconized menus. These menus perform a few of the basic activities associated with a script execution; for example, running a script, autotrace, and explain plan. With reference to the preceding screenshot of a sample SQL worksheet, the menu functions are described as follows:

• Run Statement: It executes the statement at the current cursor position.

• Run Script: It executes a script.

• Autotrace: It generates trace information about the statement.

• Explain Plan: It generates an execution plan for the query, starting at the current cursor position

• SQL Tuning Advisor: It advises the tuning tips for the current user. The user must have ADVISOR system privilege to use this icon.

• Commit: It commits the ongoing transaction in the current session.

• Rollback: It rollbacks the ongoing transaction in the current session.

• Unshared SQL Worksheet: It opens a new SQL worksheet.

• To Upper/Lower/InitCaps: It changes the string case of the statement to upper or lower or initial caps.

• Clear: It clears all the statements from the current SQL Worksheet.

• SQL History: It opens a dialog box with all the SQL statements executed for this user.

Executing a SQL statement

A SQL statement can be executed from the SQL Worksheet in three ways:

• Selecting the SQL statement and clicking on the Run Statement or Run Script icon from the Worksheet menu

• Selecting the SQL statement and pressing F9

• Terminating the SQL statement with a semicolon and pressing Ctrl + Enter

The result of the SQL statement execution is displayed in the Query Result tab. The following screenshot shows the execution of the SELECT statement using Ctrl + Enter:

Note

The SQL Worksheet doesn't supports some SQL*Plus commands such as append, archive, attribute, and break.

Calling a SQL script from SQL Developer

A SQL script saved on a specific OS location can be invoked from SQL Developer Worksheet. We will cover an overview of the two methods to execute a saved SQL script:

• A saved SQL script from an OS location can be invoked in The SQL Worksheet. It can be executed either by clicking on the Run Script (or F5) icon, or Ctrl + Enter or F9. The output of the script is displayed in the Script Output tab.

• Another option to invoke a saved script is to open it from the menu path, File | Open. Navigate to the script location and open the script. The script code would be opened in a new SQL Worksheet. Note that the worksheet's name is renamed as the actual script name. Now, the code can be executed using the Run Script icon.

A SQL script, Test_Script.sql at the C:\Labs\ location contains the following SQL statement:

SELECT * FROM EMPLOYEES
/

As shown in the following screenshot, the script has been invoked in the SQL Worksheet using SQL*Plus execute command, @:

Creating and executing an anonymous PL/SQL block

An anonymous PL/SQL block can be written and executed, as shown in the following screenshot. Note that the PL/SQL block must be terminated with a semicolon. The Script Output tab displays the confirmed status of the block execution as anonymous block completed.

The block output can be viewed in the Dbms Output Tab. This tab remains hidden until it can be enabled and activated by navigating to Menu | View | Dbms Output:

Debugging the PL/SQL code

The PL/SQL code can be debugged to observe the execution flow. The PL/SQL blocks and stored subprograms (procedures, functions, triggers, and packages) can be compiled for debugging.

An anonymous PL/SQL block can be debugged by selecting the block and choosing the Debug option from the right-click option list, as shown in the following screenshot:

Once the Debug option is clicked, the debugging starts and the following output appears in the Messages tab:

  Connecting to the database ORADEV.
  Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
  Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '3953' )
  Debugger accepted connection from database on port 3953.
  Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
  Sum of two given Numbers:35
  Process exited.
  Disconnecting from the database ORADEV.
  Debugger disconnected from database.

Note

The database user must have DEBUG CREATE SESSION and DEBUG ANY PROCEDURE privileges to debug the PL/SQL code.

Likewise, the stored subprograms can be compiled using Compile for Debug to mark them for the debugging process. Henceforth, the execution of the subprograms can be traced line by line using the Oracle supplied package, DBMS_TRACE.

Editing and saving the scripts

SQL Developer provides enhanced editing features while writing the code in the SQL Worksheet. The automated code completion suggestion in the drop-down menu effectively eases the code writing. Besides, the PL/SQL syntax highlights, member method drop-down menu, code folding, and bookmarks are the other code editing features available in the SQL Worksheet.

A stored subprogram in the schema object tree can be opened in the SQL Worksheet for editing.

The following screenshot shows the auto-code completion feature of SQL Developer. You can select any of the available options as suited for the script:

The SQL statements or PL/SQL code in the current SQL Worksheet can be saved as a text or SQL file at any specified location on the OS. Either follow the full navigation path (File | Save) or use the quick utility Save icon. Once the Windows Save dialog box appears, navigate to the target location, specify the filename, and click on the Save button.

SQL*Plus is a command-line utility interface and has been one of the primitive interfaces used by database professionals for database activities. The SQL*Plus session is similar to the SQL Worksheet of SQL Developer, where you can write and execute SQL statements and PL/SQL code.

Starting from Oracle 5.0, the SQL*Plus interface has been a part of the Oracle development kit. With regular revisions and enhancements in subsequent Oracle releases, it has been deprecated in the Oracle 11g release to recommend the use of SQL Developer. However, the SQL*Plus environment can still be established from command prompt.

The evolution cycle of SQL*Plus is shown in the following diagram:

The Oracle SQL*Plus session can be invoked from sqlplus.exe. This executable file is located in the $Oracle_home\bin folder. Alternatively, it can also be invoked by performing the following steps:

  C:\>SQLPLUS

  SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 23 14:20:36 2011
  Copyright (c) 1982, 2010, Oracle.  All rights reserved.

  Enter user-name: ORADEV/ORADEV

  Connected to:
  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  SQL>

SQL*Plus has its own set of shell commands which can be used for the execution of scripts, editing the code, and formatting the query output.

The complete set of SQL*Plus commands can be obtained by entering HELP [INDEX |?] after the SQL prompt. The complete list of SQL*Plus commands are as follows:

  SQL> HELP INDEX

  Enter Help [topic] for help.

   @             COPY         PAUSE                    SHUTDOWN
   @@            DEFINE       PRINT                    SPOOL
   /             DEL          PROMPT                   SQLPLUS
   ACCEPT        DESCRIBE     QUIT                     START
   APPEND        DISCONNECT   RECOVER                  STARTUP
   ARCHIVE LOG   EDIT         REMARK                   STORE
   ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
   BREAK         EXIT         REPHEADER                TTITLE
   BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
   CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
   CLEAR         HOST         RUN                      WHENEVER OSERROR
   COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
   COMPUTE       LIST         SET                      XQUERY
   CONNECT       PASSWORD     SHOW

Executing a SQL statement in SQL*Plus

A SQL statement can be executed in the SQL*Plus editor, terminated by a semicolon or a forward slash (/). The following screenshot demonstrates the execution of a SELECT statement. The query selects the names of all employees from the EMPLOYEES table:

Executing an anonymous PL/SQL block

Similar to the execution in SQL Developer, a PL/SQL block can be executed in SQL*Plus. The SERVEROUTPUT environment variable has to be set to ON to display the results in the editor.

The following screenshot demonstrates the execution of a PL/SQL block in SQL*Plus:

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

The following illustration shows the execution of the procedure from SQL*Plus (note that the parameter is passed using the 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.

As a common feature shared among the stored subprograms, functions can be invoked from a SQL*Plus environment and called from a PL/SQL as a procedural statement.

The following code snippet demonstrates the execution of a function from a SQL*Plus environment and its return value have been captured in a session bind variable:

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

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

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

PL/SQL procedure successfully completed.

/*Print the session variable M_NUM*/
SQL> PRINT M_NUM

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

Now, we will see the function execution from an anonymous PL/SQL block as a procedural statement:

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

SQL>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 SELECT statements; provided it must not violate the database purity levels. These rules are as follows:

Besides these rules, a standalone user-defined function must qualify the following conditions:

The F_GET_DOUBLE function can easily be embedded within a SELECT statement as it perfectly 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

In Oracle, DUAL is a table owned by the SYS user, which has a single column, DUMMY, of VARCHAR2 (1) type. It was first designed by Charles Weiss while working with internal views to duplicate a row. The DUAL table is created by default during the creation of data dictionaries with a single row, whose value is X. The users other than SYS, use its public synonym, to select the value of pseudo columns, such as USER, SYSDATE, NEXTVAL, or CURRVAL. Oracle 10g has considerably improved the performance implications of the DUAL table through a "fast dual" access mechanism.

Let us have a quick tour through the cursor management and execution cycle. Note that this execution cycle starts after the cursor has been prototyped in the declarative section:

The cursor attributes, which carry important information about the cursor processing at each stage of their execution, are as follows:

We will illustrate the usage of cursor attributes with a simple PL/SQL program. The following program implements the %ISOPEN, %NOTFOUND, and %ROWCOUNT attributes to iterate the employee data from the EMPLOYEES 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 EMPLOYEES;
   L_EMPNO EMPLOYEES.EMPNO%TYPE;
   L_ENAME EMPLOYEES.ENAME%TYPE;
BEGIN
/*Check if the cursor is already open*/
   IF NOT C_EMP%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE('Cursor is closed....Cursor has to be opened');
   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;
/

Cursor is closed....Cursor has to be opened

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.

The iterative construct, FOR loop, can be aligned to the cursor execution cycle. The benefit is that the cursor can be directly accessed without physically opening, fetching, or closing the cursor. In addition, it reduces the overhead of declaring local identifiers. The stages are handled implicitly by the FOR loop construct.

The cursor FOR loop qualifies for the best programming practices where the cursor carries multi-row set. The following program demonstrates the working of a cursor FOR loop:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare an explicit cursor to select employee name and salary*/
   CURSOR CUR_EMP IS
      SELECT ENAME, SAL
      FROM EMPLOYEES;
BEGIN
/*FOR Loop uses the cursor CUR_EMP directly*/
   FOR EMP IN CUR_EMP
   LOOP 
/*Display appropriate message*/
   DBMS_OUTPUT.PUT_LINE('Employee '||EMP.ENAME||' earns '||EMP.SAL||' per month');
   END LOOP;
END;
/

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

PL/SQL procedure successfully completed.

As the name suggests, the system-defined exceptions are defined and maintained implicitly by the Oracle server. They are defined in the Oracle STANDARD package. Whenever an exception occurs inside the program. The Oracle server matches and identifies the appropriate exception from the available set of exceptions. Majorly, these exceptions have a negative error code associated with it. In addition to the error code and error message, the system-defined exceptions have a short name which is used with the exception handlers.

For example, ORA-01422 is the error code for the TOO_MANY_ROWS exception whose error message is "exact fetch returns more than requested number of rows". But the name is required only in exception handlers.

The PL/SQL block contains a SELECT statement which selects the name and salary of an employee whose employee ID is one of the declared variables. Note that such SELECT statements are more prone to the NO_DATA_FOUND exception.

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

/*Start the PL/SQL block*/
SQL> 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 EMPLOYEES
       WHERE EMPNO = L_EMPID;
      EXCEPTION
       /*Exception Handler when no data is fetched from the table*/
       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 837

PL/SQL procedure successfully completed.

The following table consolidates some of the common system-defined exceptions along with their ORA error code:

Sometimes, the programs are expected to follow agile convention norms of an application. The programs must have standardized error codes and messages. Oracle gives flexibility in declaring and implementing your own exceptions through user-defined exceptions.

Unlike system-defined exceptions, they are raised explicitly in the BEGIN…END section 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*/
SQL> SET SERVEROUTPUT ON

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

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

/*Start the PL/SQL block*/
SQL> 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 and execute the PL/SQL block again*/
SQL> EXEC :M_DIVISOR := 2;

PL/SQL procedure successfully completed.

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*/
SQL> SET SERVEROUTPUT ON

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

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

/*Start the PL/SQL block*/
SQL> DECLARE
          /*Declare the local variables and initialize them with bind variables*/
          L_DIVISOR NUMBER := :M_DIVISOR;
          L_DIVIDEND NUMBER := :M_DIVIDEND;
          L_QUOT NUMBER;
     BEGIN
         /*Raise the exception using RAISE_APPLICATION_ERROR is the divisor is zero*/
          IF L_DIVISOR = 0 THEN
             RAISE_APPLICATION_ERROR(-20005,'Divisor cannot be equal to zero');
          END IF;
          L_QUOT := L_DIVIDEND/L_DIVISOR;
          DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT);
     EXCEPTION
         /*Print appropriate message in OTHERS exception handler*/
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
     END;
     /

ORA-20005: Divisor cannot be equal to zero

PL/SQL procedure successfully completed.

/*Assign a non zero value to M_DIVISOR and check the output of the PL/SQL block*/
SQL> EXEC :M_DIVISOR := 2;

PL/SQL procedure successfully completed.

SQL> /
The result : 5

PL/SQL procedure successfully completed.

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

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

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

/*Start the PL/SQL block*/
SQL> DECLARE
          /*Declare an exception variable*/
          NOCASE EXCEPTION;
          /*Declare the local variables and initialize them with bind variables*/
          L_DIVISOR NUMBER := :M_DIVISOR;
          L_DIVIDEND NUMBER := :M_DIVIDEND;
          L_QUOT NUMBER;
          /*Map the exception with a non predefined error number*/
          PRAGMA EXCEPTION_INIT(NOCASE,-20005);
     BEGIN
          /*Raise the exception using RAISE statement if the divisor is zero*/
          IF L_DIVISOR = 0 THEN
             RAISE_APPLICATION_ERROR(-20005,'Divisor cannot be equal to zero');
          END IF;
          L_QUOT := L_DIVIDEND/L_DIVISOR;
          DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT);
     EXCEPTION
         /*Include exception handler for NOCASE exception*/
        WHEN NOCASE THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
     END;
     /
ORA-20005: Divisor cannot be equal to zero

PL/SQL procedure successfully completed. 

Exception propagation is an important concept when dealing with nested blocks. A propagating exception always searches for the appropriate exception handler until its last host. The search starts from the EXCEPTION section of the block, that raised it, and continues abruptly until the host environment is reached. As soon as the exception handler is found, the program control resumes the normal flow.

The following cases demonstrate the propagation of exception which is raised in the innermost block:

The dependency matrix is automatically generated and maintained by the Oracle server. 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 view. The following query displays the status of our previously created functions:

/*Check the status of the function F_GET_DOUBLE*/
SQL> SELECT STATUS 
     FROM USER_OBJECTS 
     WHERE OBJECT_NAME='F_GET_DOUBLE';

STATUS
-------
VALID

DEPTREE and IDEPTREE are two views which capture and store necessary information about the direct and indirect dependencies, respectively. The views are created by a DBA by running the script from $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 as follows, to populate the dependency details of an object:

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

PL/SQL procedure successfully completed.

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

Then, the DEPTREE and IDEPTREE views can be queried to view the dependency information.

Oracle provides the data dictionary views, namely, USER_DEPENDENCIES, ALL_DEPENDENCIES, and DBA_DEPENDENCIES, to view 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:

As per 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. Though the object recompilation can easily solve the problem, it becomes a serious hindrance in working of the object validations. Dependent objects are used to fall prey to their own dependency matrix. They are rendered invalidated even if the change is not for them.

Oracle 11g brings in a fundamental change in dependency management, known as Fine Grained Dependency (FGD). The FGD concept modifies the dependency principle as if the alteration in the referenced object does not affect the dependent object, the dependent object would remain in VALID state. The new principle was received well amongst the community as it shifted the granularity from object level to element level. For instance, if a view is created with selected columns of a table and the table is altered to add a new column, the view shall remain in a valid state.