Advanced Oracle PL/SQL Developer's Guide (Second Edition)

Chapter 1. Overview of PL/SQL Programming Concepts

Structured Query Language (SQL) is a language that has been widely accepted and adopted for accessing relational databases. This language allows users to perform database operations such as reading, creating, modifying, and deleting the data. Since the summer of 1970, when Dr. E.F. Codd published the paper A Relational Model of Data for Large Shared Data Banks for the ACM journal, the language has matured comprehensively as an industry standard. With its broad range of features and easy adaptation to enterprise environments, the SQL language has been typically regarded as the most reliable language for interacting with relational databases.

PL/SQL was developed in 1991 by Oracle Corporation as a procedural language extension to SQL. Its ability to integrate seamlessly with SQL makes it a powerful language to construct the data access layer and the rich procedural extensions help in translating business logic within the Oracle Database. This first chapter introduces you to the PL/SQL language and refreshes some of the key programming concepts. The chapter is outlined as follows:

Introduction to PL/SQL
Recapitulate procedures, functions, packages, and cursors
Exception handling
Object dependencies
Major Oracle supplied packages
Oracle Development tools—SQL Developer and SQL*Plus

Introduction to PL/SQL

PL/SQL stands for Procedural Language-Structured Query Language(PL/SQL). It is part of the Oracle Database product, which means no separate installation is required. It is commonly used to translate business logic in the database and expose the program interface layer to the application. While SQL is purely a data access language that directly interacts with the database, PL/SQL is a programming language in which multiple SQLs and procedural statements can be grouped in a program unit. PL/SQL code is portable between Oracle Databases (subject to limitations imposed by versions). The built-in database optimizer refactors the code to improve the execution performance.

The advantages of PL/SQL as a language are as follows:

PL/SQL supports all types of SQL statements, data types, static SQL, and dynamic SQL
PL/SQL code runs on all platforms supported by the Oracle Database
PL/SQL code performance can be improved by the use of bind variables in direct SQL queries
PL/SQL supports the object-oriented model of the Oracle Database
PL/SQL applications increase scalability by allowing multiple users to invoke the same program unit

Although it is not used to build user interfaces, it provides the opportunity to build robust, secure, and portable interface layers, which can be exposed to a high-level programming language. Some of the key faculties of PL/SQL (PL/SQL accomplishments) are listed here:

A procedural language: A PL/SQL program can include a list of operations that can execute sequentially to get the desired result. Unlike SQL, which is just a declarative language, PL/SQL adds selective and iterative constructs to it.
Database programming language: Server side programs run faster than the middle-tier programs. Code maintenance becomes easy as it needs to be re-written less frequently.
An integral language: Application developers can easily integrate a PL/SQL program with other high-level programming interfaces such as Java, C++, or .NET. The PL/SQL procedures or subprograms can be invoked from client programs as executable statements.

PL/SQL program fundamentals

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

How do we handle an SQL execution in the program?
How do we handle the procedural execution flow in the program?
Does the program handle the exceptions?
How do we maintain (trace and debug) the PL/SQL program code?

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:

Header: This is an optional section which is required for named blocks such as procedures, functions, and triggers. It contains the program name, the program's owner, and the parameter specification.
Declaration: This is an optional section used to declare local variables, cursors, and local subprograms that are likely to be used in the program body. The DECLARE keyword indicates the beginning of the declaration section. The section can be skipped if the PL/SQL program uses no variables.
Execution: This is the procedural section of the program and comprises the main program body and an exception section. The BEGIN and END keywords indicate the beginning and end of the program body. It must contain at least one executable statement. During block execution, these statements are parsed and sequentially executed by the PL/SQL engine.
Exception: This is an optional section in the program body that contains a set of instructions as procedural statements, for various errors, that may occur in the program leading to abnormal termination. The program control lands into the exception section and the appropriate exception handler is executed. The EXCEPTION keyword indicates the start of the exception section.

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:

Anonymous PL/SQL block: This is the simplest PL/SQL program that has no name, but has its DECLARE-BEGIN-END skeleton. It can either be run for current execution as standalone block or embedded locally within a PL/SQL program unit. An anonymous block cannot be stored in the database.
Named: This block is a named PL/SQL routine that is stored persistently in the database as a schema object. It can be invoked either from a database session or by another program unit. A named PL/SQL program can be a function, procedure, trigger, or package.
Nested: A block within another PL/SQL block forms a nested block structure.

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

Note

The SERVEROUTPUT parameter is a SQL*Plus variable that enables the printing of DBMS_OUTPUT messages from a PL/SQL block.

/*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.

Cursors – an overview

Writing SQL in PL/SQL is one of the critical parts of database programming. All SQL statements embedded within a PL/SQL block are executed as a cursor. A cursor is a private memory area, temporarily allocated in the session's User Global Area (UGA), that is used for processing SQL statements. The private memory stores the result set retrieved from the SQL execution and cursor attributes. Cursors can be classified as implicit and explicit cursors.

Oracle creates an implicit cursor for all the SQL statements included in the executable section of a PL/SQL block. In this case, the cursor lifecycle is maintained by the Oracle Database.

For explicit cursors, the execution cycle can be controlled by the user. Database developers can explicitly declare an implicit cursor under the DECLARE section along with a SELECT query.

The cursor execution cycle

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.

The OPEN stage allocates the context area in the session's User Global Area for performing SQL processing. The SQL processing starts with parsing and binding, followed by statement execution. In the case of the SELECT query, the record pointer points to the first record in the result set.
The FETCH stage pulls the data from the query result set. If the result set is a multi-record set, the record pointer moves incrementally with every fetch. The fetch stage is alive until the last record is reached in the result set.
The CLOSE stage closes the cursor, flushes the context area, and releases the memory back to the UGA.

Cursor attributes

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

%ROWCOUNT: Number of rows fetched until the last fetch or impacted by the last DML operation. Applicable for SELECT as well as DML statements.
%ISOPEN: Boolean TRUE if the cursor is still open, if not FALSE. For an implicit cursor, this attribute is always FALSE.
%FOUND: Boolean TRUE, if the fetch operation switches and points to a record; if not, FALSE.
%NOTFOUND: Boolean FALSE when the cursor pointer switches but does not point to a record in the result set.

Note

%ISOPEN is the only cursor attribute that is accessible outside the cursor execution cycle.

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.

Cursor FOR loop

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.

Exception handling in PL/SQL

If a program shows an unusual and unexpected flow during runtime, which might result in abnormal termination of the program, the situation is said to be an exception. Such errors must be trapped and handled in the EXCEPTION section of the PL/SQL block. The exception handlers can suppress the abnormal termination with an alternative and secured action.

Exception handling is one of the important steps of database programming. Unhandled exceptions can result in unplanned application outages, impact business continuity, and frustrate end users.

There are two types of exceptions—system-defined and user-defined. While the Oracle Database implicitly raises a system-defined exception, a user-defined exception is explicitly declared and raised within the program unit.

In addition, Oracle provides two utility functions, SQLCODE and SQLERRM, to retrieve the error code and message for the most recent exception.

System-defined exceptions

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:

Error	Named exception	Comments (raised when:)
`ORA-00001`	`DUP_VAL_ON_INDEX`	Duplicate value exists
`ORA-01001`	`INVALID_CURSOR`	Cursor is invalid
`ORA-01012`	`NOT_LOGGED_ON`	User is not logged in
`ORA-01017`	`LOGIN_DENIED`	System error occurred
`ORA-01403`	`NO_DATA_FOUND`	The query returns no data
`ORA-01422`	`TOO_MANY_ROWS`	A single row query returns multiple rows
`ORA-01476`	`ZERO_DIVIDE`	An attempt was made to divide a number by zero
`ORA-01722`	`INVALID_NUMBER`	The number is invalid
`ORA-06504`	`ROWTYPE_MISMATCH`	Mismatch occurred in row type
`ORA-06511`	`CURSOR_ALREADY_OPEN`	Cursor is already open
`ORA-06531`	`COLLECTION_IS_NULL`	Working with `NULL` collection
`ORA-06532`	`SUBSCRIPT_OUTSIDE_LIMIT`	Collection index out of range
`ORA-06533`	`SUBSCRIPT_BEYOND_COUNT`	Collection index out of count

User-defined exceptions

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:

Declare the EXCEPTION type variable in the declaration section. Raise it explicitly in the program body using the RAISE statement. Handle it in the EXCEPTION section. Note that no error code is involved here.
Declare the EXCEPTION variable and associate it with a standard error number using PRAGMA EXCEPTION_INIT.
Note
A Pragma is a directive to the compiler to manipulate the behavior of the program unit during compilation, and not at the time of execution.
PRAGMA EXCEPTION_INIT can also be used to map an exception to a non-predefined exception. These are standard errors from Oracle but not defined as PL/SQL exceptions.
Use the RAISE_APPLICATION_ERROR to declare a dedicated error number and error message.

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.

The RAISE_APPLICATION_ERROR procedure

The RAISE_APPLICATION_ERROR is an Oracle-supplied procedure that raises a user-defined exception with a custom exception message. The exception can be optionally pre-defined in the declarative section of the PL/SQL.

The syntax for the RAISE_APPLICATION_ERROR procedure is as follows:

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

In this syntax, the error_number parameter is a mandatory parameter with the error value ranging between 20000 to 20999. error_message is the user-defined message that appears along with the exception. The last parameter is an optional argument that is used to add the exception error code to the current error stack.

The following PL/SQL program lists the employees who have joined the organization after the given date. The program must raise an exception if the date of joining is before the given date. The block uses RAISE_APPLICATION_ERROR to raise the exception with an error code 20005, and an appropriate error message appears on the screen:

/*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

In the preceding example, note that the exception name is not used to create the exception handler. Just after the exception is raised through RAISE_APPLICATION_ERROR, the program is terminated.

If you wish to have a specific exception handler for the exceptions raised through RAISE_APPLICATION_ERROR, you must declare the exception in the declarative section and associate the error number using PRAGMA EXCEPTION_INIT. Check the following PL/SQL program:

/*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.

Exception propagation

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.

Creating stored procedures

A procedure is a derivative of a PL/SQL block that has a name and is stored persistently within the database. It is the schema object that is primarily used to implement business logic on the server side. A procedure promotes a modular programming technique by breaking down complex logic into simple routines.

The key features of stored procedures are:

A procedure must be invoked from the executable section of a PL/SQL block as a procedural statement. You can also execute it directly from SQLPLUS using the EXECUTE statement. Note that a procedure can not be called from a SELECT statement.
A procedure can optionally accept parameters in IN, OUT, or IN OUT mode.
A procedure cannot return a value. The only way for a procedure to return a value is through OUT parameters, but not through the RETURN [value] statement. The RETURN statement in a procedure is used to skip the further execution of the program and exit control.

The following table differentiates between the IN, OUT, and IN OUT parameters:

`IN`	`OUT`	`IN OUT`
Default parameter mode	Has to be explicitly defined	Has to be explicitly defined
Parameter's value is passed to the program from the calling environment	Parameter returns a value back to the calling environment	Parameter may pass a value from the calling environment to the program or return value to the calling environment
Parameters are passed by reference	Parameters are passed by value	Parameters are passed by value
May be a constant, literal, or initialized variable	Uninitialized variable	Initialized variable
Can hold default value	Default value cannot be assigned	Default value cannot be assigned

The syntax for a procedure is as follows:

CREATE [OR REPLACE] PROCEDURE [Procedure Name] [Parameter List]
[AUTHID DEFINER | CURRENT_USER]
IS
  [Declaration Statements]
BEGIN
 [Executable Statements]
EXCEPTION
 [Exception handlers]
END [Procedure Name];

The following standalone procedure converts the case of the input string from lower case to upper case:

/*Create a procedure to change case of a string */
CREATE OR REPLACE PROCEDURE P_TO_UPPER (P_STR VARCHAR2)
IS
/*Declare the local variables*/
   L_STR VARCHAR2(50);
BEGIN
/*Convert the case using UPPER function*/
   L_STR := UPPER(P_STR);
/*Display the output with appropriate message*/
   DBMS_OUTPUT.PUT_LINE('Input string in Upper case : '||L_STR);
END;
/

Procedure created.

Executing a procedure

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

Similar to a stored procedure, a function is a named derivative of a PL/SQL block that is physically stored within the Oracle database schema.

The key features of stored functions are as follows:

A function can accept parameters in all three modes (IN, OUT, and IN OUT) and mandatorily returns a value.
Functions can be called in SQL statements (SELECT and DMLs). Such functions must accept only IN parameters of valid SQL types. Alternatively, a function can also be invoked from SELECT statements if the function body obeys the database purity rules.
If the function is called from an SQL statement, its return type should be a valid SQL data type. If the function is invoked from PL/SQL, the return type should be a valid PL/SQL type.
Note
Starting from Oracle Database 12c, PL/SQL—only data types can cross the PL/SQL to SQL interface. A PL/SQL anonymous block can invoke a PL/SQL subprogram with parameters of BOOLEAN or a packaged collection type.

The syntax for a function is as follows:

CREATE [OR REPLACE] FUNCTION [Function Name] [Parameter List]
RETURN [Data type]
[AUTHID DEFINER | CURRENT_USER]
[DETERMINISTIC | PARALLEL_ENABLED | PIPELINED]
[RESULT_CACHE [RELIES_ON (table name)]]
IS
  [Declaration Statements]
BEGIN
 [Executable Statements]
  RETURN [Value]
EXCEPTION
 [Exception handlers]
END [Function Name];

Let us create a standalone function, F_GET_DOUBLE, which accepts a numeric parameter and returns its double:

/*Create the function F_GET_DOUBLE*/
CREATE OR REPLACE FUNCTION F_GET_DOUBLE (P_NUM NUMBER)
RETURN NUMBER   /*Specify the return data type*/
IS

/*Declare the local variable*/
   L_NUM NUMBER;
BEGIN

/*Calculate the double of the given number*/
   L_NUM := P_NUM * 2;

/*Return the calculated value*/
   RETURN L_NUM;
END;
/

Function created.

Functions – execution methods

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.

Restrictions on calling functions from SQL expressions

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:

A function called from a SELECT statement cannot contain DML statements
A function called from an UPDATE or DELETE statement on a table cannot query (SELECT) or perform transactions (DMLs) on the same table
A function called from an SQL expression cannot contain TCL (COMMIT or ROLLBACK) commands or DDL (CREATE or ALTER) commands

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

Note

In the Oracle Database, DUAL is a table owned by the SYS user, which has a single row and 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 the data dictionary with a single row whose value is X. All database users, other than SYS, use its public synonym to select the value of pseudo columns such as USER, SYSDATE, NEXTVAL, or CURRVAL. Oracle 10g considerably improved the performance implications of the DUAL table through a fast dual-access mechanism.

A PL/SQL package

A PL/SQL package encapsulates multiple PL/SQL constructs under a single unit. The PL/SQL constructs can be subprograms, cursors, variables, and exceptions. As a schema object, a PL/SQL package demonstrates the principles of logic hiding, encapsulation, and subprogram overloading.

Note

Standalone subprograms cannot be overloaded. Only packaged subprograms can be overloaded by their signatures.

The following diagram shows the advantages of a package:

A package has two components—the package specification and package body. While the package specification contains the prototype of public constructs, the package body contains the definition of public as well as private (local) constructs.

The characteristics of the package specification are as follows:

It is the mandatory component of the package. A package cannot exist without its specification.
It contains the prototypes of public constructs. The prototype is a forward declaration of the constructs that includes the declaration, header specification and signature information terminated by a semicolon. The subprograms constructs, once prototyped, should be defined in the package body section. The package specification cannot contain an executable section.
These member constructs are visible within and outside the package. They can be invoked from outside the package by the privileged users.
Note
The public constructs of a package are accessed as [PACKAGE NAME].[CONSTRUCT].
Valid package constructs can be PL/SQL types, variables, exceptions, procedures, and functions.
If the package specification contains variables, they are implicitly initialized to NULL by Oracle

The characteristics of the package body are as follows:

The package body contains the definition of the subprograms that were declared in the package specification.
The package body can optionally contain local constructs. The accessibility of the local constructs is limited to the package body only.
The package body is an optional component; a package can exist in a database schema without its package body.

The syntax for creating a package is as follows:

CREATE [OR REPLACE] PACKAGE [NAME] IS
  [PRAGMA]
  [PUBLIC CONSTRUCTS]
END;

CREATE [OR REPLACE] PACKAGE BODY [NAME] IS
  [LOCAL CONSTRUCTS]
  [SUBPROGRAM DEFINITION]
  [BEGIN]
END;

Note the optional BEGIN section in the package body. It is optional, but gets executed only the first time the package is referenced. It is used to initialize global variables.

A package can be compiled with its specification component alone. In such cases, packaged program units cannot be invoked as their executable logic has not been defined yet.

The compilation of a package with a specification and body ensures the concurrency between the program units prototyped in the specification and the program units defined in the package body. All packaged program units are compiled in a single package compilation. If a package is compiled with errors, it is created as an invalid object in the database schema. You can query the STATUS column to check the current status of an object in the USER_OBJECTS, ALL_OBJECTS, or DBA_OBJECTS dictionary views.

Managing database dependencies

PL/SQL program units, as well as other database objects such as views, may refer to other database objects in their procedural section. The calling program unit is said to be dependent on the called program units (known as referenced objects). If EMP and DEPT are the base tables used in creating a view V_EMP_REP, then the view is dependent on EMP and DEPT.

Note

A sequence can always be a referenced object. A package body is always a dependent object.

Database dependency can be classified as direct or indirect. Consider three objects—P, M, and N. If object P references object M and object M references object N, then P is directly dependent on M and indirectly dependent on N.

Displaying the direct and indirect dependencies

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:

Login as SYSDBA in SQL Developer or SQL*Plus.
Copy the complete path and script name (prefixed with @).
Execute the script (with F9).
Query the DEPTREE and IDEPTREE views to verify their creation.

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.

Dependency metadata

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:

Dependency issues and enhancements

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.

Reviewing Oracle-supplied packages

Oracle-supplied packages exist as prebuilt programs in the database as wrapper code. These packages not only help database developers work on extended functionalities but also reduce writing extensive and complex code. The use of the Oracle-supplied API is always recommended as it improves code standardization.

The scripts for these packages are available in the $ORACLE_HOME\RDBMS\ADMIN\ folder. All packages reside on the database server. Public synonyms are available (or can be created too) for these packages so that the packages are accessible to the database users. Oracle 12c adds multiple packages to the Oracle-supplied PL/SQL package list. The latest additions provide PL/SQL interfaces for the new functionalities that have been added to the database release.

Some of the important Oracle-supplied packages are listed as follows:

DBMS_ALERT: This package is used for the notification of database events.
DBMS_LOCK: This package is used for managing lock operations (lock, conversion, and release) in PL/SQL applications.
DBMS_SESSION: This package is used to set session level preferences from PL/SQL programs (similar to ALTER SESSION).
DBMS_OUTPUT: This package is one of the most frequently used built-ins for buffering data messages and displaying debug information.
DBMS_HTTP: This package is used for HTTP callouts.
UTL_FILE: This package is used for reading, writing, and performing other file operations on the server.
UTL_MAIL: This package is used to compose and send mails.
DBMS_SCHEDULER: This package is used for scheduling execution of stored procedures at a given time.
DBMS_PARALLEL_EXECUTE: This package can be used to execute a user-defined task in parallel. If the PL/SQL block is running a large update on a table, the package can be used to enable the parallel execution of the task by splitting it into chunks.
DBMS_PRIVILEGE_CAPTURE: This package is introduced in Oracle Database 12c to set a policy in order to capture the usage of privileges (object and system) in respect to users. It helps in controlling excess privileges for users.
DBMS_REDACT: This package is introduced in Oracle Database 12c to create redaction policies, in order to mask data based on user authorization.
DBMS_RESOURCE_MANAGER: This package is used to create consumer groups, directives, and resource manager plans for containers as well as a pluggable database. The resource manager plan determines the resources allocated to a pluggable database, a schema, or a task.
DBMS_DATAPUMP: This package is used to move data, metadata, or both from one database to another. The source and target databases can be on different platforms.
DBMS_PDB: This package is introduced in Oracle Database 12c to generate or analyze the integration properties of a pluggable database for unplug/plug operations.
DBMS_SQL: This package enables dynamic SQL in PL/SQL. You can run DML or DDL statements using DBMS_SQL from a PL/SQL block.
DBMS_REDEFINITION: This package is used to perform online redefinition tasks for tables.
DBMS_UTILITY: This package is used to accomplish many utility operations such as analyze object, compile schema, get dependency, resolve a given name, validate database objects, format call and error stack, expand SQL text, and retrieve current database version.

Based on the objective to be achieved, the packages can be categorized as follows:

Standard application development: Many of the DBMS packages provide an application interface for database features. For example, DBMS_REDACT provides an interface to create and manage redaction policies. Similarly, DBMS_UTILITY provides subprograms to retrieve instance or database information, call stacks and error stacks, and analyze and validate objects. The DBMS_OUTPUT package is one of the packages most frequently used to display text messages. It can be efficiently used for tracing and debugging purposes. Accessing and writing operating system files was made possible through UTL_FILE.
General usage and application administration: Oracle has many packages for monitoring applications and users. Statistics generation, load history, and space management are the key objectives accomplished by these packages. DBMS_UTILITY comprises subprograms for general usage.
Internal support packages: Oracle maintains these packages for its own use.
Transaction processing packages: Oracle provides utility packages that enable the monitoring of transaction stages. Though they are rarely used, they can efficiently ensure transparent and smooth transactions. For example, DBMS_TRANSACTION is used to access SQL transactions from stored subprograms. DBMS_PARALLEL_EXECUTE executes a large update in parallel by splitting it into small tasks.

Oracle SQL Developer

Oracle SQL Developer is a Graphical User Interface (GUI) integrated development environment from Oracle Corporation. Oracle SQL Developer is a free tool that simplifies PL/SQL code development, the database design, and modeling and administration for standalone and cloud deployments. This tool enhances the user's experience by maximizing productivity and extensibility.

SQL Developer is a Java-based cross-platform tool that can run on Linux, Windows, and Mac OS X.

Oracle SQL Developer supports Oracle Database versions 10g, 11g, and 12c. With Oracle Database 12c Multitenant architecture, SQL Developer extends full support for various multitenant operations. In addition to the support for Oracle Databases, the tool also allows users to connect to non-Oracle Databases including MySQL, Microsoft SQL Server, Microsoft Access, Sybase, and Teradata. The extensible framework enables users to develop custom extensions in order to address specific requirements.

SQL Developer was first released in March, 2006. The initial release of SQL Developer included basic features such as a schema browser, an SQL worksheet to run SQL and invoke SQL scripts, a PL/SQL editor, code debugging, and running basic reports. Since then, the tool has grown immensely and matured over the years. At the time of writing, the latest release of SQL Developer is 4.1.

Oracle SQL Developer for DBA, Developers, and Application Architects

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.

SQL Developer 4.0

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:

Support for Oracle Database 12c Multitenant architecture
Support for Oracle NoSQL Database
Support for database products such as TimesTen, Data Miner, XML DB, and Spatial and Graphs
Support for Java 7
Querying JSON data in relational format
New instance viewer enables the monitoring of wait events, storage, log switches, and database processes

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

Practice exercise

Which of the following features are not available in SQL Developer?
1. Query builder.
2. Database export and import.
3. Database backup and recovery functions.
4. Code Subversion repository.
For a function to be called from a SQL expression, which of the following conditions should it obey?
1. A function in the SELECT statement should not contain DML statements.
2. The function should return a value.
3. A function in the UPDATE or DELETE statement should not query the same table.
4. A function called from a SQL expression cannot contain TCL (COMMIT or ROLLBACK) commands or DDL (CREATE or ALTER) commands.
The following query is executed in the SCOTT schema:
```
  SELECT NAME, referenced_owner, referenced_name
  FROM all_dependencies
  WHERE owner = USER
  AND referenced_type IN ('TABLE', 'VIEW')
  AND referenced_owner IN ('SYS')
  ORDER BY owner, NAME, referenced_owner, referenced_name;
```
Which statement is true about the output of this query?
1. It displays the schema objects, created by the user ORADEV, that use a table or view owned by SYS.
2. An exception occurs as user SCOTT has insufficient privileges to access ALL_DEPENDENCIES view.
3. It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.
4. It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS.
Which of the following is true about PL/SQL blocks?
1. Exception is a mandatory section without which an anonymous PL/SQL block fails to compile.
2. Bind variables cannot be referred inside a PL/SQL block.
3. The scope and visibility of the variables declared in the declarative section of the block are within the current block only.
4. The RAISE_APPLICATION_ERROR procedure maps a predefined error message to a customized error code.
From the following options, identify the ways of defining exceptions:
1. Declare an EXCEPTION variable and raise it using the RAISE statement.
2. Use PRAGMA EXCEPTION_INIT to associate a customized exception message to a pre-defined oracle error number.
3. Declare an EXCEPTION variable and use it in RAISE_APPLICATION_ERROR.
4. Use RAISE_APPLICATION_ERROR to create a dynamic exception at any stage within the executable or exception section of a PL/SQL block.
Choose the differences between procedures and functions:
1. A function must mandatorily return a value, while a procedure may or may not.
2. A function can be called from a SQL query, while a procedure can never be invoked from SQL.
3. A function can accept parameters passed by a value, while a procedure can accept parameters passed by reference only.
4. A standalone function can be overloaded but a procedure cannot.
Examine the values of the cursor attribute for the following query and pick the attribute with the wrong value:
```
  BEGIN
  …
  SELECT ENAME, SAL
  INTO L_ENAME, L_SAL
  FROM EMPLOYEES
  WHERE EMPID = 7900;
  …
  END;
```
1. SQL%ROWCOUNT = 1
2. SQL%ISOPEN = FALSE
3. SQL%FOUND = FALSE
4. SQL%NOTFOUND = FALSE