Oracle PL/SQL by Example, Fifth Edition (2015)

Chapter 24. Oracle-Supplied Packages


In this chapter, you will learn about

Image Extending Functionality with Oracle-Supplied Packages

Image Error Reporting with Oracle-Supplied Packages


Oracle has built into its database hundreds of packages that extend what you can achieve with PL/SQL. Each new version of the database comes with new supplied packages. With version 12c, Oracle introduced 18 brand-new packages and added new procedures in many existing packages. These packages offer functionality that you would not be able to achieve with PL/SQL alone. The reason is that the Oracle-supplied packages make use of the C programming language, which is not something that you can do with ordinary PL/SQL packages. As a consequence, Oracle-supplied packages have full access to the operating system and other aspects of the Oracle server that are not available to ordinary PL/SQL packages. You are already familiar with the DBMS_OUTPUT package’s procedure PUT_LINE, which is used to gather debugging information in the buffer for output. This chapter serves as an introduction to a few key Oracle-supplied packages; you will learn about their basic features and discover how to make use of them.

Lab 24.1: Extending Functionality with Oracle-Supplied Packages


After this lab, you will be able to

Image Access Files within PL/SQL with UTL_FILE

Image Schedule Jobs with DBMS_JOB

Image Generate an Explain Plan with DBMS_XPLAN

Image Generate Implicit Statement Results with DBMS_SQL


Accessing Files within PL/SQL with UTL_FILE

The UTL_FILE package provides text file input and output capabilities within PL/SQL. Oracle introduced this package with database version 7.3. It enables you to read input from the operating system files and write to operating system files. This capability could prove useful if you want to load data from another system into the database. For instance, if you want to store logs from a web server in your data warehouse, the UTL_FILE package would enable you to read the text file logs and then parse them so as to load the data in the correct tables and columns in the data warehouse. This package also allows you to write data out to a file. This capability is useful if you want to produce logs or capture current information about the database and store it in a text file, or extract data into a text file that another application can process.

The UTL_FILE package provides server-side text file access, so it cannot read binary files. For that purpose, you would use the DBMS_LOB package. The files that you access must be mapped to a drive on the server. The security settings that determine which directories you can access are controlled in the INIT.ORA file; you set the drives that can be accessed with the UTL_FILE_DIR initialization parameter.

UTL_FILE_DIR = 'C:\WORKING\'

You can also bypass all server-side security and allow all files to be accessed with the UTL_FILE package with the following setting:

UTL_FILE_DIR = *

If you do not have access to the INIT.ORA file on the database server, you can query the data dictionary to find the value that has been set in your database with the following SQL code:

SELECT name, value
FROM  V$SYSTEM_PARAMETER
WHERE  name = 'utl_file_dir'


By the Way

It is not advisable to allow UTL_FILE access to all files in a production environment. This setting means that all files, including important files that manage the operation of the database, are accessible. Developers may potentially write a procedure that corrupts the database in such a case.


To use the UTL_FILE file package, you open the text file, process the file by writing to it and getting lines from the file, and close the file. If you do not close the file, the operating system will think that the file is in use and will not allow you to write to the file until it is closed. Table 24.1lists the major functions, procedures, and data types in the UTL_FILE package. Table 24.2 identifies the exceptions found in this package.

Image

Image

Table 24.1 UTL_FILE Functions, Procedures, and Data Types

Image

Table 24.2 UTL_FILE Exceptions

The following example demonstrates a procedure that writes to a log file the date, time, and number of users who are currently logged on. To make use of this example, the user STUDENT needs to have privileges to access the v$session table. Access can be granted by the database administrator (DBA) to STUDENT as follows:

GRANT SELECT ON sys.v_$session TO student;
ch24_1.sql"

For Example  ch24_1.sql

CREATE OR REPLACE PACKAGE BODY school_api AS
CREATE OR REPLACE PROCEDURE LOG_USER_COUNT
  (PI_DIRECTORY  IN VARCHAR2,
   PI_FILE_NAME  IN VARCHAR2)
AS
  V_File_handle  UTL_FILE.FILE_TYPE;
  V_user_count   number;
BEGIN
  SELECT count(*)
  INTO   V_user_count
  FROM   v$session
  WHERE  username is not null;

  V_File_handle  :=
    UTL_FILE.FOPEN(PI_DIRECTORY, PI_FILE_NAME, 'A');
  UTL_FILE.NEW_LINE(V_File_handle);
  UTL_FILE.PUT_LINE(V_File_handle  , '---- User log -----');
  UTL_FILE.NEW_LINE(V_File_handle);
  UTL_FILE.PUT_LINE(V_File_handle  , 'on '||
     TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI'));
  UTL_FILE.PUT_LINE(V_File_handle  ,
     'Number of users logged on: '|| V_user_count);
  UTL_FILE.PUT_LINE(V_File_handle  , '---- End log -----');
  UTL_FILE.NEW_LINE(V_File_handle);
  UTL_FILE.FCLOSE(V_File_handle);

EXCEPTION
  WHEN UTL_FILE.INVALID_FILENAME THEN
    DBMS_OUTPUT.PUT_LINE('File is invalid');
  WHEN UTL_FILE.WRITE_ERROR THEN
    -DBMS_OUTPUT.PUT_LINE('Oracle is not able to write to file');
END;

The LOG_USER_COUNT procedure can be executed to log the number of users into the file c:\working\user.log.

SQL> exec LOG_USER_COUNT('C:\working\', 'USER.LOG');

PL/SQL procedure successfully completed.

Here are the resulting USER.LOG contents:

---- User log -----
on 07/05/03 13:09
Number of users logged on: 1
---- End log -----

Access Files with UTL_FILE

The following PL/SQL script creates a procedure to read a file and display the contents. The exception WHEN NO_DATA_FOUND will be raised when the last line of the file has been read and there are no more lines to read.

For Example  ch24_2.sql

CREATE OR REPLACE PROCEDURE READ_FILE
  (PI_DIRECTORY  IN VARCHAR2,
   PI_FILE_NAME  IN VARCHAR2)
AS
  V_File_handle  UTL_FILE.FILE_TYPE;
  V_FILE_Line    VARCHAR2(1024);
BEGIN
  V_File_handle  :=
    UTL_FILE.FOPEN(PI_DIRECTORY, PI_FILE_NAME, 'R');
   LOOP
       UTL_FILE.GET_LINE( V_File_handle , v_file_line);
       DBMS_OUTPUT.PUT_LINE(v_file_line);
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
       THEN UTL_FILE.FCLOSE(  V_File_handle );
END;

Scheduling Jobs with DBMS_JOB

The Oracle-supplied package DBMS_JOB allows you to schedule the execution of a PL/SQL procedure. It was first introduced in PL/SQL version 2.2. DBMS_JOB is an Oracle PL/SQL package provided to users. A job is submitted to a job queue and runs at the specified time. The user can also input a parameter that specifies how often the job should run. A job can consist of any PL/SQL code. The DBMS_JOB package has procedures for submitting jobs for scheduled execution, executing a job that has been submitted outside of its schedule, changing the execution parameters of a previously submitted job, suspending a job, and removing jobs from the schedule (Table 24.3). The primary reasons you might want to use this feature would be to run a batch program during off hours when there are fewer users logged into the system or to maintain a log.

Image

Table 24.3 The Main Procedures in the DBMS_JOB Package

The job queue is governed by the SNP (Snapshot Process) process that runs in the background. This process is used to implement data snapshots as well as job queues. If it fails, the database will attempt to restart the process. The database initialization parameterJOB_QUEUE_PROCESSES (set in the INIT.ORA file and viewable in the DBA view V$SYSTEM_PARAMETER) determines how many processes can start. It must be set to a number greater than 0 (the default is 0).


Watch Out!

SNP background processes will not execute jobs if the system has been started in restricted mode. It is expected behavior for jobs not to be executed while the database is in restricted mode. However, you can use the ALTER SYSTEM command to turn this behavior on and off as follows:

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;


Submitting Jobs

An important first step when submitting jobs to the queue is to ensure that your PL/SQL procedure is valid and executes the way you expect it to run. Prior to submitting a PL/SQL procedure, thoroughly test the procedure’s functionality. Job submission assumes your job is valid. TheSUBMIT procedure will take four IN parameters and return one OUT parameter (Table 24.4). The OUT parameter is the job number of the job you have submitted. This job number is also visible in the DBA_JOBS view.

Image

Table 24.4 Parameters for the DBMS_JOB.SUBMIT Procedure

The following example will submit the LOG_USER_COUNT procedure (created with ch24_3.sql) and set it to run every 6 hours.

For Example  ch24_3.sql

DECLARE
  V_JOB_NO NUMBER;
BEGIN
  DBMS_JOB.SUBMIT( JOB     => v_job_no,
                   WHAT    -=> 'LOG_USER_COUNT
                          (''C:\WORKING\'', ''USER.LOG'');',
                   NEXT_DATE => SYSDATE,
                   INTERVAL  => 'SYSDATE + 1/4 ');
  Commit;
  DBMS_OUTPUT.PUT_LINE(v_job_no);
 END;

To see this job in the queue, query the DBA_JOB view. For the STUDENT user to be able to perform this query, the DBA needs to perform the following grant:

GRANT SELECT on DBA_JOBS to STUDENT;

Running the SELECT statement

SELECT JOB, NEXT_DATE, NEXT_SEC, BROKEN, WHAT
FROM   DBA_JOBS;

then produces the following result:

JOB NEXT_DATE NEXT_SEC B WHAT
---- --------- -------- - --------------------------------------------
   1 05-JUL-03 16:56:30 N LOG_USER_COUNT('D:\WORKING', 'USER.LOG');

To force job number 1 to run or to change, use the RUN or CHANGE procedure. To remove job number 1 from the job queue, use the REMOVE procedure.

-- execute job number 1
exec dbms_job.run(1);

-- remove job number 1 from the job queue
exec dbms_job.remove(1);

-- change job number 1 to run immediately and then every hour of
-- the day
exec DBMS_JOB.CHANGE(1, null, SYSDATE, 'SYSDATE + 1/24 ');

Once the job has failed, it will be marked as broken in the job queue. Broken jobs do not run. You can also force a job to be flagged as broken. You may want to do this if you have entered all the parameters correctly yet do not want the job to run on its normal cycle while you are in the middle of altering one of its dependencies. You can then run the job again by forcing the broken flag off.

-- set job 1 to be broken
exec dbms_job.BROKEN(1, TRUE);

-- set job 1 not to be broken
exec dbms_job.BROKEN(1, FALSE);

When jobs are running, you will see their activity in the view DBA_JOBS_RUNNING. Once the run has completed, it will no longer be visible in this view.

In the following example, the procedure DELETE_ENROLL will delete a student’s enrollment if there are no grades in the GRADE table for that student and the start date of the section is already one month past the current system date.

For Example  ch24_4.sql

CREATE or REPLACE procedure DELETE_ENROLL
AS
  CURSOR C_NO_GRADES is
SELECT  st.student_id, se.section_id
  FROM  student st,
        enrollment e,
        section se
 WHERE  st.student_id = e.student_id
 AND    e.section_id  = se.section_id
 AND    se.start_date_time < ADD_MONTHS(SYSDATE, -1)
 AND  NOT EXISTS (SELECT g.student_id, g.section_id
                   FROM  grade g
                  WHERE  g.student_id = st.student_id
                    AND  g.section_id = se.section_id);
BEGIN
  FOR R in C_NO_GRADES LOOP
    DELETE  enrollment
    WHERE   section_id = r.section_id
    AND     student_id = r.student_id;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

The following script shows how to submit the procedure DELETE_ENROLL to the job queue so that it will execute once a month:

SQL> VARIABLE V_JOB NUMBER
SQL>  EXEC DBMS_JOB.SUBMIT(:v_job, 'DELETE_ENROLL;',SYSDATE,
'ADD_MONTHS(SYSDATE, 1)');

PL/SQL procedure successfully completed
SQL> commit;

Commit complete.

SQL> print v_job

     V_JOB
----------
         2

Generating an Explain Plan with DBMS_XPLAN

The DBMS_XPLAN package became available in Oracle version 9.2. This package helps to display the execution plan of an SQL statement as the output of the explain plan command. It provides the output in an easier-to-understand format than was possible in prior versions of Oracle. The SQL execution plan and runtime statistics that are stored in V$SQL_PLAN, V$SQL, and PLAN_STATISTICS are displayed with the DBMS_XPLAN package. The SQL command for creating an explain plan takes this information and uses it to populate the PLAN_TABLE. You must know a great deal about query optimization to make the most effective use of an explain plan.


By the Way

For details on SQL optimization and use of the results in an explain plan, see Oracle SQL by Example by Alice Rischert (ISBN-10: 0137142838; ISBN-13: 978-0137142835).


The DBMS_XPLAN package depends on PLAN_TABLE—a table that holds the results from running an explain plan on a SELECT statement. The following DDL is used to create the PLAN_TABLE:

create table PLAN_TABLE (
      statement_id          varchar2(30),
      plan_id               number,
      timestamp             date,
      remarks               varchar2(4000),
      operation             varchar2(30),
      options               varchar2(255),
      object_node           varchar2(128),
      object_owner          varchar2(30),
      object_name           varchar2(30),
      object_alias          varchar2(65),
      object_instance       numeric,
      object_type           varchar2(30),
      optimizer             varchar2(255),
      search_columns        number,
      id                    numeric,
      parent_id             numeric,
      depth                 numeric,
      position              numeric,
      cost                  numeric,
      cardinality           numeric,
      bytes                 numeric,
      other_tag             varchar2(255),
      partition_start       varchar2(255),
      partition_stop        varchar2(255),
      partition_id          numeric,
      other                 long,
      distribution          varchar2(30),
      cpu_cost              numeric,
      io_cost               numeric,
      temp_space            numeric,
      access_predicates     varchar2(4000),
      filter_predicates     varchar2(4000),
      projection            varchar2(4000),
      time                  numeric,
      qblock_name           varchar2(30),
      other_xml             clob
);


By the Way

The RDBMS/ADMIN/ subdirectory under your Oracle home directory will always contain the most up-to-date DDL script to create a PLAN_TABLE. You can connect as the SYSDBA to create this table and make it available to all users. The following statements will create thePLAN_TABLE under the SYS schema, create a public schema, and allow all users to make use of the PLAN_TABLE:

SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;


By default, if several plans in the plan table match the statement_id parameter that is passed to the display table function (the default value is NULL), only the plan corresponding to the last EXPLAIN PLAN command is displayed. Hence, there is no need to purge the plan table after each EXPLAIN PLAN is created. However, you should purge the plan table regularly (for example, by using the TRUNCATE TABLE command) to ensure good performance in the execution of the DISPLAY table function.

In prior versions of Oracle, a number of options were available. For example, you could use the SQL*Plus command SET AUTOTRACE TRACE EXPLAIN to generate an immediate explain plan.

SQL> SET AUTOTRACE TRACE EXPLAIN
  1  SELECT s.course_no,
  2             c.description,
  3             i.first_name,
  4             i.last_name,
  5             s.section_no,
  6             TO_CHAR(-s.start_date_time,'Mon-DD-YYYY HH:MIAM'),
  7             s.location
  8   FROM section s,
  9        course c,
 10        instructor i
 11   WHERE s.course_no    = c.course_no
 12*  AND   s.instructor_id= i.instructor_id

Execution Plan
-------------------------------------------------------------
  0    SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=78 Bytes=4368)
  1   0  HASH JOIN (Cost=9 Card=78 Bytes=4368)
  2   1    HASH JOIN (Cost=6 Card=78 Bytes=2574)
  3   2     TABLE ACCESS (FULL) OF 'INSTRUCTOR' (Cost=3 Card=10 Bytes=140)
  4   2     TABLE ACCESS (FULL) OF 'SECTION' (Cost=3 Card=78 Bytes=1482)
  5   1    TABLE ACCESS (FULL) OF 'COURSE' (Cost=3 Card=30 Bytes=690)

You can also generate an explain plan that will be stored in the PLAN_TABLE and then query the results of an explain plan.

SQL> explain plan for
  2  SELECT s.course_no,
  3             c.description,
  4             i.first_name,
  5             i.last_name,
  6             s.section_no,
  7             TO_CHAR(s.start_date_time,'Mon-DD-YYYY HH:MIAM'),
  8             s.location
  9    FROM section s,
 10         course c,
 11         instructor i
 12    WHERE s.course_no    = c.course_no
 13    AND   s.instructor_id= i.instructor_id;

Explained.

select rtrim ( lpad  ( ' ', 2*level )  ||
                rtrim ( operation )     || ' ' ||
                rtrim ( options )       || ' ' ||
                object_name             || ' ' ||
                partition_start         || ' ' ||
                partition_stop          || ' ' ||
                to_char ( partition_id )
                ) the_query_plan
  from plan_table
  connect by prior id = parent_id
  start with id = 0;


THE_QUERY_PLAN
-----------------------------------------
  SELECT STATEMENT
   HASH JOIN
    HASH JOIN
     TABLE ACCESS BY INDEX ROWID SECTION
      INDEX FULL SCAN SECT_INST_FK_I
     SORT JOIN
      TABLE ACCESS FULL INSTRUCTOR
    TABLE ACCESS FULL COURSE

To make use of the DBMS_XPLAN procedure, use the SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) command to generate the explain plan.

SQL> explain plan for
  2  SELECT s.course_no,
  3             c.description,
  4             i.first_name,
  5             i.last_name,
  6             s.section_no,
  7             TO_CHAR(s.start_date_time,'Mon-DD-YYYY HH:MIAM'),
  8             s.location
  9    FROM  section s,
 10          course c,
 11          instructor i
 12    WHERE s.course_no    = c.course_no
 13    AND   s.instructor_id= i.instructor_id;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------

---------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   78  |  4368 |   9   (34)| *00:00:01|
|*  1 |  HASH JOIN         |             |   78  |  4368 |   9   (34)| 00:00:01 |
|*  2 |  HASH JOIN         |             |   78  |  2574 |   6   (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL|  INSTRUCTOR |   10  |   140 |   3   (34)| 00:00:01 |
|   4 |   TABLE ACCESS FULL|  SECTION    |   78  |  1482 |   3   (34)| 00:00:01 |
|   5 |  TABLE ACCESS FULL |  COURSE     |   30  |   690 |   3   (34)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("S"."COURSE_NO"="C"."COURSE_NO")
  2 - access("S"."INSTRUCTOR_ID"="I"."INSTRUCTOR_ID")

17 rows selected.

Generating Implicit Statement Results with DBMS_SQL

In older versions of Oracle, there were a few operations available in other database products such as Microsoft SQL Server that could not be done as elegantly in the Oracle platform. This created a challenge for companies that were migrating their applications to Oracle because they would have to make a great many changes to their stored procedures—even rewrite them—for use on the Oracle platform. Consider the ability to pass the results of a SQL statement out of a stored procedure. This can be done rather easily with the Transact SQL (T-SQL) procedural language of SQL Server, but in the Oracle platform it had to be done with a REF CURSOR parameter. This is because T-SQL syntax permits implicit returns of a SQL result set from queries. A similar kind of functionality is now allowed in Oracle 12c through use of the DBMS_SQL package and theRETURN_RESULT procedure in that system package.

Using DBMS_SQL to Return a Result Set

The Oracle-supplied package DBMS_SQL includes an entity called a SQL cursor number. This PL/SQL integer can be passed as an IN or OUT parameter. You should use the DBMS_SQL package to run dynamic SQL when you don’t know the details of the SELECT statement until it is run or you don’t know which columns will be called in the SELECT statement until it is run.

In the following example, DBMS_SQL.RETURN_RESULT is used to return a result set without any OUT parameter.

For Example  ch24.6.sql

CREATE OR REPLACE PACKAGE Student_Instructor AS
PROCEDURE show_population
      (i_zip IN zipcode.zip%TYPE);
END Student_Instructor;
/

CREATE or REPLACE PACKAGE BODY Student_Instructor
AS
PROCEDURE show_population
     (i_zip IN zipcode.zip%TYPE)
AS
  student_list    SYS_REFCURSOR;
  instructor_list SYS_REFCURSOR;
BEGIN
  OPEN student_list FOR
        SELECT 'Student' type, First_Name, Last_Name
          FROM  student
         WHERE  zip = i_zip;
      DBMS_SQL.RETURN_RESULT(student_list);
  OPEN instructor_list FOR
          SELECT 'Instructor' type, First_Name, Last_Name
            FROM  instructor
           WHERE  zip = i_zip;
      DBMS_SQL.RETURN_RESULT(instructor_list);
END show_population;
END Student_Instructor;
/

This script can be executed for the ZIP code 10025 as follows:

SQL> exec Student_Instructor.show_population('10025');

It produces the following result:

PL/SQL procedure successfully completed.

ResultSet #1

TYPE    FIRST_NAME                LAST_NAME
------- ------------------------- -------------------------
Student Jerry                     Abdou
Student Nicole                    Gillen
Student Frank                     Pace

ResultSet #2

TYPE       FIRST_NAME                LAST_NAME
---------- ------------------------- -------------------------
Instructor Tom                       Wojick
Instructor Nina                      Schorin
Instructor Todd                      Smythe
Instructor Charles                   Lowry

Lab 24.2: Error Reporting with Oracle-Supplied Packages


After this lab, you will be able to

Image Use the DBMS_UTILITY Package for Error Reporting

Image Use the UTL_CALL_STACK Package for Error Reporting


In Chapters 89, and 10, you explored various techniques for handling and reporting errors in your programs. The examples that you have seen so far are quite simple—that is, a single script that handles one or multiple exceptions. Oftentimes when working with applications, multiple programming units may be calling each other as well as passing control of the execution to a middle tier or a front end. In such circumstances, proper error reporting is important. Without it, both developers and users may encounter all sorts of problems.

In PL/SQL, two predefined packages may be used for this purpose: DBMS_UTILITY and UTL_CALL_STACK.

Using the DBMS_UTILITY Package for Error Reporting

The DBMS_UTILITY package contains various utilitarian subprograms, some of which enhance error-reporting capabilities. These error-reporting functions are described in Table 24.5.

Image

Table 24.5 Error Reporting with the DBMS_UTILITY Package

FORMAT_CALL_STACK

As mentioned earlier, the FORMAT_CALL_STACK function formats and returns the current call stack up to 2000 bytes. It has the syntax shown in Listing 24.1.

Listing 24.1 FORMAT_CALL_STACK

DBMS_UTILITY.FORMAT_CALL_STACK
RETURN VARCHAR2;

The next example illustrates the use of the FORMAT_CALL_STACK function. Note that it employs stored procedures.

For Example  ch24_7.sql

CREATE OR REPLACE PROCEDURE first
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);
END first;
/

CREATE OR REPLACE PROCEDURE second
IS
BEGIN
  first;
END second;
/

CREATE OR REPLACE PROCEDURE third
IS
BEGIN
  second;
END third;
/

BEGIN
  third;
END;

The preceding script creates three procedures: FIRST, SECOND, and THIRD. The FIRST procedure calls the FORMAT_CALL_STACK function, which then returns the call stack of the current execution. The SECOND procedure makes a call to the FIRST procedure, and the THIRDprocedure makes a call to the SECOND procedure. Finally, the anonymous PL/SQL block at the end of the example makes a call to the THIRD procedure. When run, this example produces the following output:

----- PL/SQL Call Stack -----
  object   line    object
  handle  number   name
0x104a93040        4  procedure STUDENT.FIRST
0xa06f8208         4  procedure STUDENT.SECOND
0x1045f1e68        4  procedure STUDENT.THIRD
0xa0259658         2  anonymous block

 This call stack reveals the sequence of procedure invocations and should be read from the bottom up. First, the anonymous PL/SQL block calls THIRD, which in turn calls SECOND, which in turn calls FIRST. Finally, the FIRST procedure calls the FORMAT_CALL_STACK function.

FORMAT_ERROR_BACKTRACE

The FORMAT_ERROR_BACKTRACE function formats and returns the error backtrace (up to 2000 bytes) associated with the current error. It has the syntax shown in Listing 24.2.

Listing 24.2 FORMAT_ERROR_BACKTRACE

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
RETURN VARCHAR2;

Typically, the FORMAT_ERROR_BACKTRACE function is called inside the exception handler, as illustrated by the next example.

For Example  ch24_8.sql

CREATE OR REPLACE PROCEDURE first
IS
  v_name VARCHAR2(30);
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure FIRST');

  SELECT RTRIM(first_name)||' '||RTRIM(last_name)
    INTO v_name
    FROM student
   WHERE student_id = 1000;
END first;
/

CREATE OR REPLACE PROCEDURE second
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure SECOND');
  first;
END second;
/

CREATE OR REPLACE PROCEDURE third
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure THIRD');
  second;
END third;
/
BEGIN
  third;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

Similar to the previous example, this script uses stored procedures to illustrate the behavior of the FORMAT_ERROR_BACKTRACE function. The FIRST procedure employs a SELECT INTO statement for the nonexistent student ID. The procedure itself does not have an exception handler; instead, the exception handler appears within the anonymous PL/SQL block. When run, this example produces the following output:

procedure THIRD
procedure SECOND
procedure FIRST
ORA-06512: at "STUDENT.FIRST", line 7
ORA-06512: at "STUDENT.SECOND", line 5
ORA-06512: at "STUDENT.THIRD", line 5
ORA-06512: at line 2

 The first three lines of the output are produced by the DBMS_OUTPUT.PUT_LINE statements placed in each procedure. The last four lines of the output are produced by the FORMAT_ERROR_BACKTRACE function. Note how this error backtrace demonstrates the flow of the execution to the point where the exception occurred. Essentially, it tells us that the exception is found on line 7 in the procedure FIRST. As in the previous example, the output of this function should be read from the bottom up.

Only one item is missing in the output of this example—the exception itself. In essence, you are able to follow the execution path all the way to the precise line number where the exception occurred, but you do not know which exception has occurred. In this very simple example, it is easy to deduce that the problem is a NO_DATA_FOUND exception. In a more complex environment, however, you may be presented with much more intricate code or may not be as familiar with the table structures. In these circumstances, it is essential to know which exception was raised—and the FORMAT_ERROR_STACK function is able to answer this question.

FORMAT_ERROR_STACK

The FORMAT_ERROR_STACK function formats and returns the current error stack up to 2000 bytes. It has the syntax shown in Listing 24.3.

Listing 24.3 FORMAT_ERROR_STACK

DBMS_UTILITY.FORMAT_ERROR_STACK
RETURN VARCHAR2;

Like the FORMAT_ERROR_BACKTRACE function, the FORMAT_ERROR_STACK function is called inside the exception handler as well. This is illustrated by the modified version of the previous example.

For Example  ch24_9.sql

CREATE OR REPLACE PROCEDURE first
IS
  v_name VARCHAR2(30);
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure FIRST');
  SELECT RTRIM(first_name)||' '||RTRIM(last_name)
    INTO v_name
    FROM student
   WHERE student_id = 1000;
END first;
/

CREATE OR REPLACE PROCEDURE second
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure SECOND');
  first;
END second;
/

CREATE OR REPLACE PROCEDURE third
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure THIRD');
  second;
END third;
/

BEGIN
  third;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.PUT_LINE ('Error Backtrace:');
    DBMS_OUTPUT.PUT_LINE ('----------------');
    DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    DBMS_OUTPUT.PUT_LINE ('Error Stack:');
    DBMS_OUTPUT.PUT_LINE ('----------------');
    DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

When run, this version of the script produces the following output:

procedure THIRD
procedure SECOND
procedure FIRST
Error Backtrace:
----------------
ORA-06512: at "STUDENT.FIRST", line 7
ORA-06512: at "STUDENT.SECOND", line 5
ORA-06512: at "STUDENT.THIRD", line 5
ORA-06512: at line 2
Error Stack:
----------------
ORA-01403: no data found

This version of the output provides you with the flow of execution up to the point where the exception occurred. It also identifies the error number and error message associated with that exception.

Using the UTL_CALL_STACK Package for Error Reporting

The UTL_CALL_STACK package is a new built-in package introduced in Oracle 12c. It consists of a set of functions that provide various pieces of information on execution and error stacks, including subroutine and unit names and individual line numbers for dynamic depths. Some of these functions are described in Table 24.6. For a complete list of the UTL_CALL_STACK subprograms, refer to the Oracle Database PL/SQL Packages and Types Reference available online.

Image

Table 24.6 Error Reporting with the UTL_CALL_STACK Package

To enhance error reporting, the functions listed in Table 24.6 are typically used in conjunction with other subroutines defined in the UTL_CALL_STACK package. These combinations are illustrated further by the examples in this lab.

DYNAMIC_DEPTH

The DYNAMIC_DEPTH function returns the number of subprograms in the current call stack. It has the syntax shown in Listing 24.4.

Listing 24.4 DYNAMIC_DEPTH

UTL_CALL_STACK.DYNAMIC_DEPTH
RETURN PLS_INTEGER;

The use of this function is illustrated by the following example.

For Example  ch24_10.sql

CREATE OR REPLACE PROCEDURE first
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure FIRST');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
END first;
/

CREATE OR REPLACE PROCEDURE second
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure SECOND');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  first;
END second;
/

CREATE OR REPLACE PROCEDURE third
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure THIRD');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  second;
END third;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE ('anonymous block');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  third;
END;

This example creates three procedures: FIRST, SECOND, and THIRD. Each procedure and the anonymous PL/SQL block call the DYNAMIC_DEPTH function, which then returns the number of subprograms in the call stack of the current execution. When run, this example produces the following output:

anonymous block
dynamic depth: 1
procedure THIRD
dynamic depth: 2
procedure SECOND
dynamic depth: 3
procedure FIRST
dynamic depth: 4

 This output illustrates the dynamic depth concept. The PL/SQL block is the currently executing subprogram; its dynamic depth is 1. Essentially, this is what has started the execution stack. When this block invokes the THIRD procedure, the dynamic depth of this procedure becomes 2. Basically, this is what was executed second in the call stack. Similarly, the dynamic depth of the SECOND procedure is 3, as it was executed third. Finally, the dynamic depth of the FIRST procedure is 4, as it was executed last.

Backtrace Depth, Unit, and Line Functions

The backtrace set of functions returns various backtrace data from the point where an exception was thrown to the point where the backtrace is examined. The syntax of the backtrace functions is shown in Listing 24.5.

Listing 24.5 Backtrace Functions

UTL_CALL_STACK.BACKTRACE_DEPTH
RETURN PLS_INTEGER;

UTL_CALL_STACK.BACKTRACE_LINE (backtrace_depth IN PLS_INTEGER)
RETURN PLS_INTEGER;

UTL_CALL_STACK.BACKTRACE_UNIT (backtrace_depth IN PLS_INTEGER)
RETURN VARCHAR2;

The use of the backtrace functions is illustrated by the modified version of the earlier example. In this version, the FIRST procedure has been modified to cause a VALUE_ERROR exception, and the PL/SQL block has been extended with an exception-handling section. All changes are shown in bold.

For Example  ch24_11.sql

CREATE OR REPLACE PROCEDURE first
IS
  v_string VARCHAR2(3);
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure FIRST');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  v_string := 'ABCDEF';
END first;
/
CREATE OR REPLACE PROCEDURE second
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure SECOND');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  first;
END second;
/

CREATE OR REPLACE PROCEDURE third
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure THIRD');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  second;
END third;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE ('anonymous block');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  third;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.PUT_LINE (CHR(10)||'Backtrace Stack: '||CHR(10)||RPAD('-', 15, '-'));
    DBMS_OUTPUT.PUT_LINE ('Backtrace Depth: '||TO_CHAR(UTL_CALL_STACK.BACKTRACE_DEPTH));
    DBMS_OUTPUT.PUT_LINE ('Backtrace Line: ' ||

  TO_CHAR(UTL_CALL_STACK.BACKTRACE_LINE(UTL_CALL_STACK.BACKTRACE_DEPTH)));
    DBMS_OUTPUT.PUT_LINE ('Backtrace Unit: ' ||
      UTL_CALL_STACK.BACKTRACE_UNIT(UTL_CALL_STACK.BACKTRACE_DEPTH));
END;

Note how the value returned by the BACKTRACE_DEPTH function is used as an input parameter to the BACKTRACE_LINE and BACKTRACE_UNIT functions. When run, this script produces the following output:

anonymous block
dynamic depth: 1
procedure THIRD
dynamic depth: 2
procedure SECOND
dynamic depth: 3
procedure FIRST
dynamic depth: 4

Backtrace Stack:
---------------
Backtrace Depth: 4
Backtrace Line: 7
Backtrace Unit: STUDENT.FIRST

The backtrace stack reports that an exception was encountered in the backtrace depth 4, on line number 7 in the subroutine called FIRST in the STUDENT schema. This is very detailed backtrace output for such a simple example, yet it is still missing the exception itself. The set of error functions described next covers this exception-reporting gap.

Error Depth, Message, and Number Functions

Another set of error functions returns the error depth, message, and number of an error in the current stack. They have the syntax shown in Listing 24.6.

Listing 24.6 Error Functions

UTL_CALL_STACK.ERROR_DEPTH
RETURN PLS_INTEGER;

UTL_CALL_STACK.ERROR_MSG (error_depth IN PLS_INTEGER)
RETURN VARCHAR2;

UTL_CALL_STACK.ERROR_NUMBER (error_depth IN PLS_INTEGER)
RETURN VARCHAR2;

Next consider how these functions may be utilized for error reporting. In this version of the script, the exception-handling section includes calls to these functions. All changes are shown in bold.

For Example  ch24_12.sql

CREATE OR REPLACE PROCEDURE first
IS
  v_string VARCHAR2(3);
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure FIRST');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  v_string := 'ABCDEF';
END first;
/

CREATE OR REPLACE PROCEDURE second
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure SECOND');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  first;
END second;
/

CREATE OR REPLACE PROCEDURE third
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('procedure THIRD');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  second;
END third;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE ('anonymous block');
  DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
  third;
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.PUT_LINE (CHR(10)||'Backtrace Stack: '||CHR(10)||RPAD('-', 15, '-'));
    DBMS_OUTPUT.PUT_LINE ('Backtrace Depth: '||TO_CHAR(UTL_CALL_STACK.BACKTRACE_DEPTH));
    DBMS_OUTPUT.PUT_LINE ('Backtrace Line: ' ||
TO_CHAR(UTL_CALL_STACK.BACKTRACE_LINE(UTL_CALL_STACK.BACKTRACE_DEPTH)));
   DBMS_OUTPUT.PUT_LINE ('Backtrace Unit: ' ||
     UTL_CALL_STACK.BACKTRACE_UNIT(UTL_CALL_STACK.BACKTRACE_DEPTH));
   DBMS_OUTPUT.PUT_LINE (CHR(10)||'Error Info: '||CHR(10)||RPAD('-', 15, '-'));
    DBMS_OUTPUT.PUT_LINE ('Error Depth: '  ||TO_CHAR(UTL_CALL_STACK.ERROR_DEPTH));
   DBMS_OUTPUT.PUT_LINE ('Error Number: ' ||
     TO_CHAR(UTL_CALL_STACK.ERROR_NUMBER (UTL_CALL_STACK.ERROR_DEPTH)));
   DBMS_OUTPUT.PUT_LINE ('Error Message: '||
     UTL_CALL_STACK.ERROR_MSG(UTL_CALL_STACK.ERROR_DEPTH));
END;

When run, this example produces the following output:

anonymous block
dynamic depth: 1
procedure THIRD
dynamic depth: 2
procedure SECOND
dynamic depth: 3
procedure FIRST
dynamic depth: 4

Backtrace Stack:
---------------
Backtrace Depth: 4
Backtrace Line: 7
Backtrace Unit: STUDENT.FIRST

Error Info:
---------------
Error Depth: 1
Error Number: 6502
Error Message: PL/SQL: numeric or value error: character string buffer too small

Note how the output now contains the error depth, number, and message. In a more complex environment, this type of trace data can provide invaluable insight for PL/SQL developers—insight that is essential in diagnosing and resolving problems in PL/SQL code efficiently.

This lab has covered only some of the functions of the UTL_CALL_STACK package. For additional information on how to utilize this package fully, refer to the Oracle Database PL/SQL Packages and Types Reference available online.

Summary

In this chapter, you learned about a variety of Oracle-supplied packages that can be used to extend the functionality of your programs. The strategy of accessing files on the operating system within a stored procedure by making use of UTL_FILE was reviewed. You also learned how to analyze SQL by making use of the explain plan generated by DBMS_XPLAN. In addition, you saw how to generate implicit statement results with DBMS_SQL. The chapter concluded with a discussion of the use of DBMS_UTILITY and UTL_CALL_STACK for error reporting.