Oracle Advanced PL/SQL Developer Professional Guide

5 (1 reviews total)
By Saurabh K. Gupta
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Overview of PL/SQL Programming Concepts

About this book

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database. Server-side PL/SQL is stored and compiled in the Oracle Database and runs within the Oracle executable. With this guide Oracle developers can work towards accomplishing Oracle 11g Advanced PL/SQL Professional certification, which is the second milestone for developers working at the Associate level.

The Oracle Advanced PL/SQL Developer Professional Guide helps you master advanced PL/SQL concepts. Besides the clear and precise explanation on advanced topics, it also contains example code and demonstrations, which gives a sense of application and usage to readers.

The book gives a deep insight that will help transform readers from mid-level programmers to professional database developers. It aims to cover the advanced features of PL/SQL for designing and optimizing PL/SQL code.

This book starts with an overview of PL/SQL as the programming database language and outlines the benefits and characteristics of the language. The book then covers the advanced features that include PL/SQL code writing using collections, tuning recommendations using result caching, implementing VPD to enforce row level security, and much more. Apart from programming, the book also dives deep into the usage of the development tool SQL Developer, employing best practices in database environments and safeguarding the vulnerable areas in PL/SQL code to avoid code injection.

Publication date:
May 2012
Publisher
Packt
Pages
440
ISBN
9781849687225

 

Chapter 1. Overview of PL/SQL Programming Concepts

In the summer of 1970, Dr. E.F. Codd published his paper, A Relational Model of Data for Large Shared Data Banks, for the ACM journal. The projected model was accepted and subsequently an interactive database language, SQL, was developed by IBM Corporation, Inc. In 1979, Relational Software, Inc. stepped into the commercial implementation of SQL as the primary RDBMS language. Later, Relational Software, Inc. transformed into Oracle and since then, its story has been a success.

The Structured Query Language or SQL (pronounced "Sequel") has been used as the primary interactive language for all data operations such as selection, creation, and manipulation. Besides data operations, the language has administrative and monitoring features which ensure data consistency, integrity, and object controllability. By virtue of its multifaceted and versatile behavior in data centric environments, all major RDBMS support SQL as a database interaction language. The universal acceptance of SQL eases the logical usability across the databases (such as MySQL and SQL Server) with minor syntactical modifications.

Over the initial years of exploration, the procedural limitations of SQL were identified which prevented it from being an efficient programming language amongst the fourth generation languages. The head to head competition and demanding expectations of the industry led to the evolution of a procedural version of SQL in the Oracle database family. The first version of PL/SQL was debuted in Oracle 6.0 (in 1991) as an optional procedural extension in SQL* forms. Since its induction, PL/SQL has emerged as a strong and proven database programming language. With the release of Oracle 11g database (in 2007), PL/SQL has successfully stepped into its 11.0 version.

In this chapter, we will tour the Oracle PL/SQL programming concepts to get an overview of PL/SQL block, subprograms, exception handling, and object dependencies. The chapter outlines the benefits and characteristics of the language in the following sections:

  • Introduction to PL/SQL

  • Oracle development tools—SQL Developer and SQL*Plus

  • Recapitulate procedures, functions, packages, and cursors

  • Exception handling

  • Object dependencies

  • Major Oracle supplied packages

 

PL/SQL—the procedural aspect


PL/SQL stands for Procedural Language-Structured Query Language. It is a significant member of the Oracle programming toolset and extensively used to code server-side programs. Some of its major accomplishments are that it:

  • Supports programming constructs to design a program unit

  • Implements business logic in an Oracle server using cursors and database objects such as packages, subprograms, and many more

  • Makes the application portability easier

  • Preserves execution privileges and transaction management

  • Makes use of advanced PL/SQL features such as collections to process bulk data and enhance performance

  • Allows external programs to be executed from PL/SQL

As a language, the different perceptions of PL/SQL are as follows:

  • An interactive and structured language: The PL/SQL language comprises of a glossary of expressive and explanatory keywords. The self-indenting, structured feature, and ANSI compatibility ensures quick learning and adaptation for an individual.

  • An embedded language : A PL/SQL program is not environment-dependent but can be easily invoked from any recognized Oracle development environment such as SQL* Plus, SQL Developer, TOAD, reporting tools, and so on.

  • An integral language : A database manager can easily integrate a PL/SQL server-side program with other client-side programming interfaces such as Java, C++, or .NET. The PL/SQL procedures or subprograms can be invoked from client programs as executable statements.

My first PL/SQL program

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

  • Anonymous: This block is an unnamed PL/SQL block which is persistent for single execution only

  • Named: This block contains named PL/SQL programs which are stored physically in the database as schema objects

  • Nested: A block within another PL/SQL block forms a nested block structure

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

  • Header: This is an optional section which is required for the named blocks. It contains block name, block owner's name, parameter specifications, and return type specification (for functions).

  • Declaration: This is an optional section which is used for declaration of local variables, cursors, and local subprograms. The DECLARE keyword indicates the beginning of the declaration section.

  • Execution: This is the mandatory section of a PL/SQL block which contains the executable statements. These statements are parsed by the PL/SQL engine and executed on the block invocation. The BEGIN and END keywords indicate the beginning and end of an executable section.

  • Exception: This is the optional section of the block which contains the exception handlers. The appropriate exception handler is activated upon any exception raised from the executable section to suggest alternate steps. The EXCEPTION keyword indicates the start of the exception section.

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

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

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

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

Note

The SERVEROUTPUT parameter is a SQL*Plus variable which enables the printing of block debug messages. It is discussed in detail in the SQL*Plus section.

/*Start the PL/SQL block*/
DECLARE
/*Declare a local variable and initialize with a default value*/
   L_NUM NUMBER := 15;
   L_RES NUMBER;
BEGIN
/*Calculate the double of local variable*/
L_RES := L_NUM *2;
/*Print the result*/
   DBMS_OUTPUT.PUT_LINE('Double of '||TO_CHAR(L_NUM)||' is '||TO_CHAR(L_RES));
END;
/
Double of 15 is 30

PL/SQL procedure successfully completed.
 

PL/SQL development environments


Oracle provides and recommends the usage of its development tools for SQL writing and code execution. This chapter will cover the two main developer tools from Oracle:

  • SQL Developer

  • SQL*Plus

However, there are many SQL development interfaces available on the Web such as TOAD from Quest Software, Dreamcoder by Mentat Technologies, and so on.

SQL Developer

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

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

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

The key accomplishments offered by SQL Developer are:

  • Authenticating and connecting to multiple Oracle databases

  • Allowing creation and maintenance of schema objects packages, procedures, functions, triggers, indexes and views

  • Querying and manipulating the data

  • Database utilities such as version maintenance, admin activities, migration, and database export

  • Support for SQL*Plus commands

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

  • Connection browser and Schema browser

  • SQL Worksheet and Query Builder

  • Database import and export utility wizard

  • Database user-defined reports

  • Code repository configuration for version control

  • Database copy and migration utility wizard

  • Third-party databases

  • Oracle APEX integration

  • TimesTen integration

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

SQL Developer—the history

The following flowchart demonstrates the release history of SQL Developer:

Creating a connection

Once the SQL Developer tool is downloaded from the Oracle Technology Network (OTN) website (in ZIP format), it is ready for use and does not require any installation. The target server can be Oracle 11g database software. For educational and practice purposes, Oracle recommends the usage of the Oracle Database Express edition. It can be downloaded for free from the following URL:

http://www.oracle.com/technetwork/database/express-edition/overview/index.html

By default, the database software installation takes care of the Oracle database configuration and Oracle network configuration.

Now, we shall start working with SQL Developer to connect to the database. The first and foremost step is establishing the connection to the target database.

The steps for creating a connection in SQL Developer are as follows:

  1. Double-click on \\sqldeveloper\sqldeveloper.exe.

  2. Go to Menu | View | Connections. A tabbed page titled Connections will appear at the left-hand side of the page. The top node of the tree is Connections.

  3. Right-click on the Connections node and select New Connection… to open the connection wizard.

  4. Specify the connection name, username, password, connection type, role (DBA or default), host name, port number, and SID of the target database. Connection type must be Basic if you specify the connection parameters. If TNS, then select a connection string from the Network Alias drop-down list (which is in sync with the TNSNAMES.ORA file)

  5. Check the Save Password option to allow the Connection wizard to remember the password of this user.

  6. Click on the Test button to verify the connection. The status (success or error message) will appear in the wizard's console.

  7. Click on the Connect button to connect to the database. By default, it opens a SQL Worksheet to write and execute queries.

SQL Worksheet

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

A new worksheet can be opened in two ways:

  • Hitting the shortcut key, Alt + F10

  • Navigating to Tools | SQL Worksheet

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

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

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

  • Run Script: It executes a script.

  • Autotrace: It generates trace information about the statement.

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

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

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

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

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

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

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

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

Executing a SQL statement

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

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

  • Selecting the SQL statement and pressing F9

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

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

Note

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

Calling a SQL script from SQL Developer

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

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

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

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

SELECT * FROM EMPLOYEES
/

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

Creating and executing an anonymous PL/SQL block

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

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

Debugging the PL/SQL code

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

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

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

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

Note

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

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

Editing and saving the scripts

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

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

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

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

SQL*Plus

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

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

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

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

  1. Open command prompt.

  2. Enter SQLPLUS, press Enter. Note the SQL*Plus welcome message. The editor will prompt for a username and password.

  3. Enter the username, password, and database connection string. Press Enter.

  4. Connect to the database:

  C:\>SQLPLUS

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

  Enter user-name: ORADEV/ORADEV

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

  SQL>

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

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

  SQL> HELP INDEX

  Enter Help [topic] for help.

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

Executing a SQL statement in SQL*Plus

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

Executing an anonymous PL/SQL block

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

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

 

Procedures


A procedure is a derivative of PL/SQL block structure which is identified by its own specific name. It is stored as a schema object in the database and implements business logic in the applications. For this reason, procedures are often referred to as Business Managers of PL/SQL which not only maintain the business logic repository, but also demonstrate solution scalability and a modular way of programming.

The characteristics of procedures are as follows:

  • A procedure can neither be called from a SELECT statement nor can it appear as a right-hand operand in an assignment statement. It has to be invoked from the executable section of a PL/SQL block as a procedural statement.

  • They can optionally accept parameters in IN, OUT, or IN OUT mode.

  • This implies that the only possibility 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 exit the procedure and skip the further execution.

For recapitulation, 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 into 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 a value to the calling environment

Parameters are passed by reference

Parameters are passed by value

Parameters are passed by value

May be constant, literal, or initialized variable

Uninitialized variable

Initialized variable

Can hold the 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 lowercase to uppercase:

/*Create a procedure to convert the string from lower case to upper case*/
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 be either executed from SQL*Plus or from a PL/SQL block. The P_TO_UPPER procedure can be executed from SQL*Plus.

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

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

/*Declare a session variable for the input*/
SQL> VARIABLE M_STR VARCHAR2(50);
/*Assign a test value to the session variable*/
SQL> EXECUTE :M_STR := 'My first PLSQL procedure';

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

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

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

Input string in Upper case : MY FIRST PLSQL PROCEDURE

PL/SQL procedure successfully completed.
 

Functions


Like a procedure, a function is also a derivative of a PL/SQL block structure which is physically stored within a database. Unlike procedures, they are the "workforce" in PL/SQL and meant for calculative and computational activities in the applications.

The characteristics of functions are as follows:

  • Functions can be called from 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.

  • Functions can accept parameters in all three modes (IN, OUT, and IN OUT) and mandatorily return a value. The type of the return value must be a valid SQL data type (not be of BOOLEAN, RECORD, TABLE, or any other PL/SQL data 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 | PIPELINES]
[RESULT_CACHE [RELIES_ON (table name)]]
IS
  [Declaration Statements]
BEGIN
 [Executable Statements] 
  RETURN [Value]
EXCEPTION
 [Exception handlers]
END [Function Name];

The standalone function, F_GET_DOUBLE, accepts a single argument 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.

Function—execution methods

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

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

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

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

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

PL/SQL procedure successfully completed.

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

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

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

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

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

PL/SQL procedure successfully completed.

Restrictions on calling functions from SQL expressions

Unlike procedures, a stored function can be called from SELECT statements; provided it must not violate the database purity levels. These rules are as follows:

  • A function called from a SELECT statement cannot contain DML statements

  • A function called from a UPDATE or DELETE statement on a table cannot query (SELECT) or perform transaction (DMLs) on the same table

  • A function called from SQL expressions cannot contain the TCL (COMMIT or ROLLBACK) command or the DDL (CREATE or ALTER) command

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

  • The parameters to the stored function, if any, should be passed in "pass by reference" mode that is, IN parameter only. The data type of the parameter must be a valid SQL data type. Also, the parameters must follow positional notation in the list.

  • The return type of the function must be a valid SQL data type.

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

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

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

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

 

PL/SQL packages


Packages are the database objects which behave as libraries and grounds on the principle of encapsulation and data hiding. A package is privileged to contain a variety of constructs such as subprograms, variables, cursors, exceptions, and variables. In addition, it enjoys multiple add-on features such as subprogram overloading, public and private member constructs, and so on.

Note

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

The following diagram shows the advantages of a package:

A package has two components—package specification and package body. While 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 package specification are as follows:

  • Package specification is the mandatory component of the package. A package cannot exist without its specification.

  • Package specification contains the prototypes of the constructs. The prototype is the forward declaration of the constructs which would be referenced later in the package body. The subprogram (procedure and function) prototype includes the signature information with a semicolon. The subprograms, once prototyped, must have their definition in the package body section. The package specification cannot contain an executable section.

  • These member constructs enjoy their visibility 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].

  • The valid package constructs can be PL/SQL types, variables, exceptions, procedures, and functions.

  • If 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 which were declared in package specification.

  • The package body can optionally contain local constructs. The visibility scope 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 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]
END;

Note the optional BEGIN-END block in the package body. It is optional, but gets executed only the first time the package is referenced. They are used for initialization of 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 specification and body ensures the concurrency between the program units prototyped in the specification and the program units defined in the package body. All the packaged program units are compiled in the single package compilation. If the package is compiled with errors, it is created as an invalid object in the database. The USER_OBJECTS dictionary view is used to query the status of a schema object. The STATUS column in the view shows the current status as VALID or INVALID.

 

Cursors—an overview


Cursors make a concrete conceptual ground for database professionals. In simple words, a cursor is a memory pointer to a specific private memory location where a SELECT statement is processed. This memory location is known as a context area.

Every SQL statement in a PL/SQL block can be realized as a cursor. The context area is the memory location which records the complete information about the SQL statement currently under process. The processing of the SQL statement in this private memory area involves its parsing, data fetch, and retrieval information. The data retrieved should be pulled into local variables and, henceforth, used within the program.

On the basis of their management, cursors are classified as implicit and explicit cursors.

The Oracle server is fully responsible for the complete execution cycle of an implicit cursor. Oracle implicitly creates a cursor for all SQL statements (such as SELECT, INSERT, UPDATE, and DELETE) within the PL/SQL blocks.

For explicit cursors, the execution cycle is maneuvered by database programmers. Explicit cursors are meant only for the SELECT statements which can fetch one or more rows from the database. The developers have the complete privilege and control to create a cursor, fetch data iteratively, and close the cursor.

Cursor execution cycle

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

  • The OPEN stage allocates the context area in Process Global Area (PGA) for carrying out further processing (parsing, binding, and execution) of the SELECT statement associated with the cursor. In addition, the record pointer moves to the first record in the data set.

  • The FETCH stage pulls the data from the query result set. If the result set is a multi-record set, the pointer increments with every fetch. The Fetch stage is live 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 PGA.

Cursor attributes

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

  • %ROWCOUNT: Number of rows returned/changed in the last executed query. Applicable for SELECT as well as DML statements.

  • %ISOPEN: Boolean TRUE if the cursor is still open, else FALSE. For an implicit cursor, it is only FALSE.

  • %FOUND: Boolean TRUE, if the fetch operation switches and points to a record, else 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 which is accessible outside the cursor execution cycle.

We will illustrate the usage of cursor attributes with a simple PL/SQL program. The following program implements the %ISOPEN, %NOTFOUND, and %ROWCOUNT attributes to iterate the employee data from the EMPLOYEES table and display it:

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

/*Start the PL/SQL Block*/
DECLARE

/*Declare a cursor to select employees data*/
   CURSOR C_EMP IS
      SELECT EMPNO,ENAME
	   FROM EMPLOYEES;
   L_EMPNO EMPLOYEES.EMPNO%TYPE;
   L_ENAME EMPLOYEES.ENAME%TYPE;
BEGIN
/*Check if the cursor is already open*/
   IF NOT C_EMP%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE('Cursor is closed....Cursor has to be opened');
   END IF;
/*Open the cursor and iterate in a loop*/
   OPEN C_EMP;
   LOOP
/*Fetch the cursor data into local variables*/
   FETCH C_EMP INTO L_EMPNO, L_ENAME;
   EXIT WHEN C_EMP%NOTFOUND;
/*Display the employee information*/
      DBMS_OUTPUT.PUT_LINE(chr(10)||'Display Information for employee:'||C_EMP%ROWCOUNT);
      DBMS_OUTPUT.PUT_LINE('Employee Id:'||L_EMPNO);
      DBMS_OUTPUT.PUT_LINE('Employee Name:'||L_ENAME);
   END LOOP;
END;
/

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

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

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

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

Display Information for employee:4
Employee Id:7566
Employee Name:JONES
….

PL/SQL procedure successfully completed.

Cursor FOR loop

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

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

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

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

PL/SQL procedure successfully completed.
 

Exception handling in PL/SQL


During runtime, the abnormal program flow which occurs within a precompiled program unit with the actual data is known as an exception. Such errors can be trapped in the EXCEPTION section of a PL/SQL block. The exception handlers within the section can capture the appropriate error and redirect the program flow for an alternative or final task. An efficient exception handling ensures safe and secure termination of the program. The situation without exceptions may become serious if the program involves transactions and the program doesn't handle the appropriate exception, thus ending up in abrupt termination of the program.

There are two types of exceptions—system-defined exceptions and user defined exceptions. While system defined exceptions are implicitly raised by the Oracle server, user-defined exceptions follow different ways to be explicitly raised within the program.

In addition, Oracle avails two utility functions, SQLCODE and SQLERRM, to retrieve the error code and message for the last occurred exception.

System-defined exceptions

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

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

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

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

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

No Employee exists with the id 837

PL/SQL procedure successfully completed.

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

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

A number is attempted to divide 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

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

Unlike system-defined exceptions, they are raised explicitly in the BEGIN…END section using the RAISE statement.

There are three ways of declaring user-defined exceptions:

  • 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 here no error code is involved.

  • Declare the EXCEPTION variable and associate it with a standard error number using PRAGMA EXCEPTION_INIT.

    Note

    A Pragma is a clue 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 the Oracle server, but not defined as PL/SQL exceptions.

  • Use the RAISE_APPLICATION_ERROR to declare own 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*/
SQL> SET SERVEROUTPUT ON

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

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> /
The result : 5

PL/SQL procedure successfully completed.

The RAISE_APPLICATION_ERROR procedure

Oracle gives privilege to the database programmers to create their own error number and associate an error message, too. These are dynamic user defined exceptions and are done through an Oracle-supplied method, RAISE_APPLICATION_ERROR. It can be implemented either in the executable section to capture specific and logical errors, or it can be used in the exception section to handle errors of a generic nature.

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 formal parameter whose value must be in the range of -20000 to -20999. The second parameter, error_message, corresponds to the error number and appears with the exception when raised in the program. The last parameter is the optional parameter which allows the error to be added to the current error stack. By default, its value is FALSE.

The following program rewrites the last program by creating a user-defined exception, dynamically (note that it doesn't have the EXCEPTION type variable):

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

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

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

ORA-20005: Divisor cannot be equal to zero

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> /
The result : 5

PL/SQL procedure successfully completed.

As soon as the exception is raised through RAISE_APPLICATION_ERROR, the program control skips the further execution and jumps to the EXCEPTION section. As there is no exception name mapped against this error code, only OTHERS exception handler can handle the exception.

If a EXCEPTION variable has been declared and mapped to the same user-defined error number, the exception handler can be created with the exception variable. Let us rewrite the preceding program to include an exception variable and suitable exception handler. The following program demonstrates the working of user-defined exceptions and dynamic user-defined exceptions in a single program:

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

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

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed. 

Exception propagation

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

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

  • Case 1: The following diagram shows the state of a nested PL/SQL block. The inner block raises an exception which is handled in its own EXCEPTION section:

    Exception A is raised by the inner block. The inner block handles the exception A within its scope. After the exception is handled, the program control resumes the flow with statements after the inner block in the outer block.

  • Case 2: The following diagram shows the state of a nested PL/SQL block where the inner block raises an exception but does not handle the same in its own EXCEPTION section. The EXCEPTION section of the outer block handles the raised exception:

    The inner block raises the exception A but does not handle it, so it gets propagated to the EXCEPTION section of the enclosing outer block. Note the abrupt skipping of statements in the outer block.

    Now, the outer block handles the exception A. The exception propagated from the inner block is handled in the outer block and is then terminated.

  • Case 3: The following diagram shows the state of a nested PL/SQL block where both the inner and outer block doesn't handle the exception raised in the inner block:

    Handling for exception A is missing in the inner as well as the outer block. As a result, the unhandled exception error is raised. The exception is propagated to the host with an error message and the program is terminated abruptly.

 

Managing database dependencies


Oracle objects which avail the standings and services of other objects are dependent on them. Suppose, a complex view, V, is created on two tables, T1 and T2. The object, V, is dependent on T1 and T2, while T1 and T2 are the referenced objects. Therefore, as a thumb rule of dependency, a valid database object can either be a dependent or a referenced object. The thumb rule of dependency has some exceptions for synonyms and the package body. While synonyms can always be referenced objects, the package body is always a dependent object.

Database dependency can be classified as direct or indirect. Consider three objects—P, M, and N. If P references M and M references N, then P is directly dependent on M. In the same case, P and N share indirect dependency. Schema objects can refer tables, views, sequences, procedures, functions, packages specification, triggers, and synonyms in their definitions and can behave as both dependent and referenced objects. Out of these, a sequence can appear as a referenced object only, while package body can only be a dependent object.

Displaying the direct and indirect dependencies

The dependency matrix is automatically generated and maintained by the Oracle server. The status of an object is the basis of dependency among the objects. The status of an object can be queried from the USER_OBJECTS view. The following query displays the status of our previously created functions:

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

STATUS
-------
VALID

DEPTREE and IDEPTREE are two views which capture and store necessary information about the direct and indirect dependencies, respectively. The views are created by a DBA by running the script from $ORACLE_HOME\RDBMS\ADMIN\utldtree.sql

The execution steps for the script are as follows:

  1. Login as SYSDBA in SQL Developer or SQL*Plus.

  2. Copy the complete path and script name (prefix with @).

  3. xecute the script (with F9).

  4. 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 as follows, to populate the dependency details of an object:

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

PL/SQL procedure successfully completed.

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

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

Dependency metadata

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

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

Dependency issues and enhancements

As per the conventional dependency phenomenon, the status validity of the dependent object depends upon the status of the referenced object. So, if the definition of the referenced object is altered, the dependent object is marked INVALID in the USER_OBJECTS view. Though the object recompilation can easily solve the problem, it becomes a serious hindrance in working of the object validations. Dependent objects are used to fall prey to their own dependency matrix. They are rendered invalidated even if the change is not for them.

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

 

Reviewing Oracle-supplied packages


Oracle-supplied packages are provided by the Oracle server and inbuilt in the database as a wrapper code. These packages not only facilitate the database programmers to work on extended functionalities but also reduce writing extensive and complex code. Use of Oracle-supplied API is always recommended as it improves the code standardization, too.

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 for these packages so that these packages are accessible to all users. Until Oracle 11g, more than 1000 packages were available and this count tends to increase with every database release.

Some of the important packages are listed as follows:

  • DBMS_ALERT: This package is used for notification of database events

  • DBMS_LOCK: This package is used for managing the lock operations (lock, conversion, 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 of data messages and display debug information

  • DBMS_HTTP: This package is used for HTTP callouts

  • UTL_FILE: This package is used for reading, writing and 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

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

  • Standard application development: DBMS_OUTPUT is the most frequently used package to display the required text. It is used for tracing and debugging purposes. Accessing and writing OS files was made possible through UTL_FILE. Similarly, system dependent binary files are accessed through the DBMS_LOB package.

The Oracle supplied packages often try to access SQL features which is their other big advantage.

  • General usage and application administration: The Oracle server has many packages to monitor the applications and users. Stats generation, load history, and space management are the key objectives accomplished by these packages

  • Internal support packages: Oracle maintains these packages for its own use.

  • Transaction processing packages: Oracle provides utility packages which enables the monitoring of transaction stages. Though they are rarely used, but could efficiently ensure transparent and smooth transactions. For example, DBMS_TRANSACTION.

Among these categories, standard application development packages are the most frequently used ones.

 

Summary


We toured the fundamentals of PL/SQL programming. Starting with a small flashback on evolution of PL/SQL, we understood its working with Oracle Development tools such as SQL Developer and SQL*Plus.

We had an overview of cursor handling and major Oracle schema objects such as procedures, functions, and packages. Thereafter, we refreshed the error management in PL/SQL through exception handlers using server-defined, user-defined, and dynamic exceptions.

In the upcoming chapters, we will discuss programming guidelines and advanced PL/SQL concepts in details. In the next chapter, we will cover cursor handling in detail and usage guidelines for various types of cursors.

 

Practice exercise


  1. 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

  2. For a function to be called from the 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 SQL expressions cannot contain the TCL (COMMIT or ROLLBACK) command or the DDL (CREATE or ALTER) command.

  3. The following query is executed in the ORADEV 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 which use a table or view owned by SYS.

    2. Exception occurs as user ORADEV 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.

  4. 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 is within the current block only.

    4. The RAISE_APPLICATION_ERROR procedure maps a predefined error message to a customized error code.

  5. From the following options, identify the ways of defining exceptions:

    1. Declare a 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 a 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.

  6. Chose 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 SQL queries, while a procedure can never be invoked from SQL.

    3. A function can accept parameters passed by value, while a procedure can accept parameters as passed by reference only.

    4. A standalone function can be overloaded but a procedure cannot.

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

About the Author

  • Saurabh K. Gupta

    Saurabh K. Gupta is a seasoned database technologist with extensive experience in designing high performance and highly available database applications. His technology focus has been centered around Oracle Database architecture, Oracle Cloud platform, Database In-Memory, Database Consolidation, Multitenant, Exadata, Big Data, and Hadoop. He has authored the first edition of this book. He is an active speaker at technical conferences from Oracle Technology Network, IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his twitter handle (or SAURABHKG) or through his technical blog www.sbhoracle. wordpress.com, with comments, suggestions, and feedback regarding this book.

    Browse publications by this author

Latest Reviews

(1 reviews total)
Es muy bueno, lo estoy practicando todavía. PL/SQL nunca acaba, siempre hay más por aprender. Y lo recomiendo tanto como para un principiante como para un experto.
Oracle Advanced PL/SQL Developer Professional Guide
Unlock this book and the full library for FREE
Start free trial