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

---

In Chapters 8, 9, 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.

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.

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.