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
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.
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
andEND
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.
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.
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 theSELECT
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 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 forSELECT
as well as DML statements.%ISOPEN
: BooleanTRUE
if the cursor is still open, if notFALSE
. For an implicit cursor, this attribute is alwaysFALSE
.%FOUND
: BooleanTRUE
, if the fetch operation switches and points to a record; if not,FALSE
.%NOTFOUND
: BooleanFALSE
when the cursor pointer switches but does not point to a record in the result set.
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.
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.
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:) |
---|---|---|
|
|
Duplicate value exists |
|
|
Cursor is invalid |
|
|
User is not logged in |
|
|
System error occurred |
|
|
The query returns no data |
|
|
A single row query returns multiple rows |
|
|
An attempt was made to divide a number by zero |
|
|
The number is invalid |
|
|
Mismatch occurred in row type |
|
|
Cursor is already open |
|
|
Working with |
|
|
Collection index out of range |
|
|
Collection index out of count |
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 theRAISE
statement. Handle it in theEXCEPTION
section. Note that no error code is involved here.Declare the
EXCEPTION
variable and associate it with a standard error number usingPRAGMA 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
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.
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 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 aSELECT
statement.A procedure can optionally accept parameters in
IN
,OUT
, orIN
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 theRETURN
[value]
statement. TheRETURN
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:
|
|
|
---|---|---|
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.
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.
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
, andIN
OUT
) and mandatorily returns a value.Functions can be called in SQL statements (
SELECT
and DMLs). Such functions must accept onlyIN
parameters of valid SQL types. Alternatively, a function can also be invoked fromSELECT
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.
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 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.
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 statementsA function called from an
UPDATE
orDELETE
statement on a table cannot query (SELECT
) or perform transactions (DMLs) on the same tableA function called from an SQL expression cannot contain TCL (
COMMIT
orROLLBACK
) commands or DDL (CREATE
orALTER
) 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 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.
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.
Oracle Database Release 12c includes a number of PL/SQL feature enhancements. These enhancements are focused on improving the usability of PL/SQL as a language. Although the next chapter will discuss many more new features in detail, it is worthwhile to mention a few of them that are exclusively related to Oracle PL/SQL subprograms.
Defining PL/SQL subprograms in the
SELECT
statement: Although PL/SQL allows the invoking of a function from theSELECT
statement, the context switch from SQL to the PL/SQL engine degraded the performance. Oracle 12c allows creating PL/SQL units in theWITH
clause of a subquery and using it in theSELECT
statement. The new approach to calling functions in SQL statements enhances the performance as there is no context switching across the engines. In addition, these functions are not stored in the database schema.Granting roles to program units: One of the challenges in PL/SQL before Oracle 12c was that a program unit had to be created with definers rights, if it was intended to be executed by all users. A user with a lower set of privileges could perform the unauthorized changes. With Oracle 12c, granting roles to PL/SQL program units adds a levels of safety. You can now create program units with invoker's rights and control the privileges, which are required to run the program, through a role.
Protecting PL/SQL unit access through the
ACCESSIBLE
BY
clause: With Oracle 12c, you can restrict access to a PL/SQL unit by unauthorized programs. A subprogram (a procedure, function or a package) can optionally include anACCESSIBLE
BY
clause to define a white list of PL/SQL program units that can invoke it.
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
.
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.
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
andIDEPTREE
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.
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-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 toALTER SESSION
).DBMS_OUTPUT
: This package is one of the most frequently used built-ins for buffering data messages and displaying debug information.UTL_FILE
: This package is used for reading, writing, and performing other file operations on the server.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 usingDBMS_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. TheDBMS_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 throughUTL_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 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 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:
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.
Over the years, PL/SQL has matured a great deal and has produced a vast library of objects, features, and standards. This chapter focused on giving a quick summary of PL/SQL programming. It assumed readers were familiar with database programming concepts and provided enough substance to get them ready for the forthcoming chapters. It would have been a tough call to build a glossary of PL/SQL objects in a single chapter.
We started with an overview of PL/SQL fundamentals, block structure, and exception handling. Additionally, this chapter threw light on cursor handling in PL/SQL, the CURSOR
FOR
loop, and schema objects such as procedures, functions, and packages. In forthcoming chapters, we will focus on the key faculties of the PL/SQL language as well as Oracle Database 12c features.
Which of the following features are not available in SQL Developer?
Query builder.
Database export and import.
Database backup and recovery functions.
Code Subversion repository.
For a function to be called from a SQL expression, which of the following conditions should it obey?
A function in the
SELECT
statement should not contain DML statements.The function should return a value.
A function in the
UPDATE
orDELETE
statement should not query the same table.A function called from a SQL expression cannot contain TCL (
COMMIT
orROLLBACK
) commands or DDL (CREATE
orALTER
) 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?
It displays the schema objects, created by the user
ORADEV
, that use a table or view owned bySYS
.An exception occurs as user
SCOTT
has insufficient privileges to accessALL_DEPENDENCIES
view.It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.
It displays only those PL/SQL code objects created by the user
OE
that reference a table or view created by the userSYS
.
Which of the following is true about PL/SQL blocks?
Exception is a mandatory section without which an anonymous PL/SQL block fails to compile.
Bind variables cannot be referred inside a PL/SQL block.
The scope and visibility of the variables declared in the declarative section of the block are within the current block only.
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:
Declare an
EXCEPTION
variable and raise it using theRAISE
statement.Use
PRAGMA
EXCEPTION_INIT
to associate a customized exception message to a pre-defined oracle error number.Declare an
EXCEPTION
variable and use it inRAISE_APPLICATION_ERROR
.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:
A function must mandatorily return a value, while a procedure may or may not.
A function can be called from a SQL query, while a procedure can never be invoked from SQL.
A function can accept parameters passed by a value, while a procedure can accept parameters passed by reference only.
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;
SQL%ROWCOUNT
=
1
SQL%ISOPEN
=
FALSE
SQL%FOUND
=
FALSE
SQL%NOTFOUND
=
FALSE