"Go" oralce PL/SQL stack information tracking

Summarized as follows:

dbms_utility. Format_call_stack -This was introduced in Oracle7, dbms_utility. Format_call_stack this built-in function returns a formatted string that shows the execution call stack: The sequence of calls to all procedures or functions at the call point of this function. In other words, the function answers the question: "How did I get here?" ”

dbms_utility. Format_error_stack -This was introduced in Oracle7, dbms_utility. Format_error_stack this built-in function, like SQLERRM, returns the error message associated with the current error (the value returned by Sqlcode).

dbms_utility. Format_error_backtrace -This was introduced in the Oracle 10g database, dbms_utility. The Format_error_backtrace built-in function returns a formatted string stack, and the program in the stack and its line number can be traced back to the row where the error was first thrown.

Transferred from: http://www.itpub.net/thread-1896005-1-1.html

Complex call-Stack analysis

Original link: http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html
Steven Feuerstein (Oracle ACE Director)


The Utl_call_stack package in the Oracle 12c database gives developers a better answer.
This is the third and final article on the new features of PL/SQL in Oracle 12c database Release 1, which will focus on the new Utl_call_stack package.

Call stack, error stack, and error backtracking

Prior to the Oracle 12c database, the Oracle database provided several dbms_utility functions that were extremely useful in answering several key questions that programmers asked when developing, troubleshooting, and maintaining their programs. However, there is still room for improvement, which is why the Oracle 12c database joins the Utl_call_stack package.

Before I dive into the Utl_call_stack, let's review the three dbms_utility functions, which are re-conceived by the Utl_call_stack package.

Dbms_utility. Format_call_stack. This is introduced in the Oracle7, dbms_utility. Format_call_stack this built-in function returns a formatted string that shows the execution call stack: The sequence of calls to all procedures or functions at the call point of this function. In other words, the function answers the question: "How did I get here?" ”

Code Listing 1 shows the dbms_utility. Format_call_stack functions and examples of formatted substrings.

Code Listing 1:dbms_utility. The display of the Format_call_stack function
sql> CREATE OR REPLACE PROCEDURE proc1
2 is
3 BEGIN
4 Dbms_output.put_line (Dbms_utility.format_call_stack);
5 END;
6/

Sql> CREATE OR REPLACE Package Pkg1
2 is
3 PROCEDURE proc2;
4 END Pkg1;
5/

Sql> CREATE OR REPLACE Package BODY pkg1
2 is
3 PROCEDURE PROC2
4 is
5 BEGIN
6 proc1;
7 END;
8 END Pkg1;
9/

sql> CREATE OR REPLACE PROCEDURE proc3
2 is
3 BEGIN
4 for Indx in 1.. 1000
5 LOOP
6 NULL;
7 END LOOP;
8
9 pkg1.proc2;
Ten END;
11/

Sql> BEGIN
2 proc3;
3 END;
4/

——————— PL/SQL call Stack ———————
Object handle line Number object name
000007ff7ea83240 4 procedure HR. PROC1
000007FF7E9CC3B0 6 Package Body HR. PKG1
000007ff7ea0a3b0 9 procedure HR. PROC3
000007FF7EA07C00 2 anonymous block

This is useful information for trace and error logs, but uses dbms_utility. Format_call_stack and its returned string also have some drawbacks:

If you call a subroutine in a package, the formatted call stack will only display the name of the package, not the subroutine's name, and of course it will not show the subroutine name nested within that subroutine.

If you only need the recently executed subroutine name, you have to parse the string. It's not hard, but you have to write and maintain more code.

The value of this "object handle" is all "noise" for all practical purposes. PL/SQL Programmers (at least, programmers outside of Oracle) never use this value.

Dbms_utility. Format_error_stack. This is introduced in the Oracle7, dbms_utility. Format_error_stack this built-in function, like SQLERRM, returns the error message associated with the current error (the value returned by Sqlcode).

Dbms_utility. The Format_error_stack function and SQLERRM differ in two ways:

It can return error messages up to 1,899 characters, which avoids the problem of information truncation when the error stack grows (or at least minimizes the likelihood). SQLERRM will truncate the message leaving only 510 characters.

You cannot pass an error code to this function, nor can it be used to return an error message that is represented by an error code.

According to the rules, you should call this function in your exception handler and then save the error stack in your error log table for post-mortem analysis.

Dbms_utility. Format_error_backtrace. This is introduced in the Oracle 10g database, dbms_utility. The Format_error_backtrace built-in function returns a formatted string stack, and the program in the stack and its line number can be traced back to the row where the error was first thrown.

This function filled a big ditch in the l/sql. In the Oracle9i database and earlier versions, once you have handled the exception in a PL/SQL block, you cannot determine which line the error occurred in (this is probably the most important information for the developer).


If you really want to see this information, you have to allow exceptions to be handled, and you can see that the full error backtracking information is displayed on the screen or otherwise presented to the user.

Dbms_utility. Format_error_backtrace generated and useful information. I suggest that whenever you deal with an error, you call dbms_utility. The Format_error_backtrace function and writes trace information to your error log table. It can help when it comes to solving the cause of the error.

However, just like dbms_utility. Like the Format_call_stack function, the key information (the name of the subroutine and the number of rows that went wrong) is hidden within the formatted string. And, worse, you don't see the name of the subroutine inside the package.

All of these flaws have been addressed in the new package Utl_call_stack in the Oracle 12c database.

The new Utl_call_stack Package


The Utl_call_stack package provides information about the sub-programs that are now executing. Although the name of the package seems to provide only the execution stack, it also provides access to the error stack and error backtracking information.

Each stack contains a depth (position), and you can ask for a specific depth of information for each of these three stacks, which is visible throughout the package. This means that you no longer need to parse the formatted string to find the specific information you need.

Utl_call_stack for Dbms_utility. One of the most important improvements in format_call_stack is that you can get a unit-qualified name, which is a concatenation of the name of the unit, the name of all the parent programs, and the subroutine name. However, there is no such additional information in the error backtracking stack. Table 1 contains a list of the subroutines in the Utl_call_stack package and their descriptions.


Sub-Program Name description
Backtrace_depth returns the number of elements in the backtracking stack
Backtrace_line the line number of the program unit that returns the specified depth
Backtrace_unit the name of the program unit that returns the specified depth
Concatenate_subprogram returns the name of the program unit qualified by the stitching form
Dynamic_depth returns the number of subroutines in the call stack, including calls to SQL, Java, and other non-PL-SQL context calls along the way--for example, suppose a calls B calls C Call B, and if the stack is written in a row, it looks like this (the dynamic depth below):

A b C B
4 3 2 1

Error_depth returns the number of errors in the call stack
ERROR_MSG returns the error message for the specified depth
Error_number returns the error code for the specified depth
Lexical_depth returns the lexical nesting level for subroutines that specify dynamic depth
Unit_line the line number of the program unit that returns the specified depth
Subprogram returns the specified depth of the program unit-qualified name


Table 1:utl_call_stack Sub-programs in packages

First, let's take a look at how to use Utl_call_stack to simulate dbms_utility. The Format_call_stack function and displays the complete call stack. To do this, you must traverse the entries in the stack with depth. The format_call_stack_12c process in Listing 2 precisely accomplishes this task.

The code listing 2:FORMAT_CALL_STACK_12C procedure called the Utl_call_stack subroutine

sql> CREATE OR REPLACE PROCEDURE format_call_stack_12c
2 is
3 BEGIN
4 Dbms_output.put_line (
5 ' lexdepth Depth lineno Name ');
6 Dbms_output.put_line (
7 '-----------------------');
8
9 for the_depth in REVERSE 1..
Ten utl_call_stack.dynamic_depth ()
LOOP
Dbms_output.put_line (
Rpad (
Utl_call_stack.lexical_depth (
The_depth),
16 9)
17 | | Rpad (the_depth, 5)
18 | | Rpad (
To_char (
Utl_call_stack.unit_line (
The_depth),
22 ' 99 '),
23 8)
24 | | Utl_call_stack.concatenate_subprogram (
Utl_call_stack.subprogram (
(the_depth)));
END LOOP;
The END;
29/

Here are a few key calls to the Utl_call_stack package in code Listing 2:

Lines 9th and 10th set the For loop, using the Dynamic_depth function, starting with the last element in the stack, iterating through the first element in the stack.

Line 14th calls the Lexical_depth function to display the depth of each element in the stack.

Lines 20th and 21 call Unit_line to get the line number of the program unit.

Lines 24th and 25th call Subprogram first to get the elements of the current depth in the stack. Then use Concatenate_subprogram to get the complete qualified name of the subroutine.

I then used the format_call_stack_12c in Code Listing 2 in the Pkg.do_stuff process and executed the process, as shown in Listing 3.

The code listing 3:pkg.do_stuff procedure called the format_call_stack_12c procedure
Sql> CREATE OR REPLACE Package pkg
2 is
3 PROCEDURE Do_stuff;
4 END;
5/

Sql> CREATE OR REPLACE Package BODY pkg
2 is
3 PROCEDURE Do_stuff
4 is
5 PROCEDURE NP1
6 is
7 PROCEDURE NP2
8 is
9 PROCEDURE NP3
Ten is
BEGIN
format_call_stack_12c;
The END;
BEGIN
NP3;
The END;
BEGIN
NP2;
The END;
BEGIN
NP1;
The END;
The END;
24/

Sql> BEGIN
2 Pkg.do_stuff;
3 END;
4/

Lexdepth Depth Lineno Name
——————— ——————— ———————— ——————————————————————————
0 6 2 __anonymous_block
1 5 PKG. Do_stuff
2 4 PKG. Do_stuff. NP1
3 3 PKG. Do_stuff. NP1. NP2
4 2 PKG. Do_stuff. NP1. NP2. NP3
0 1 format_call_stack_12c

Next I'll use the Utl_call_stack package to display the name and line number of the program unit that throws the current exception. In code Listing 4, I created and executed a function called backtrace_to, which "hides" the call to the Utl_call_stack subroutine. In each call to Backtrace_unit and Backtrace_line, I passed in the return value of the Error_depth function.

The code listing 4:backtrace_to function called the Utl_call_stack subroutine

sql> CREATE OR REPLACE FUNCTION backtrace_to
2 RETURN VARCHAR2
3 is
4 BEGIN
5 RETURN
6 Utl_call_stack.backtrace_unit (
7 utl_call_stack.error_depth)
8 | | ' Line '
9 | |
Ten Utl_call_stack.backtrace_line (
Utl_call_stack.error_depth);
The END;
13/

Sql> CREATE OR REPLACE Package Pkg1
2 is
3 PROCEDURE Proc1;
4 PROCEDURE proc2;
5 END;
6/

Sql> CREATE OR REPLACE Package BODY pkg1
2 is
3 PROCEDURE Proc1
4 is
5 PROCEDURE Nested_in_proc1
6 is
7 BEGIN
8 RAISE Value_error;
9 END;
Ten BEGIN
One by one nested_in_proc1;
The END;
13
PROCEDURE PROC2
Is
BEGIN
Proc1;
EXCEPTION
OTHERS then RAISE No_data_found;
The END;
Pkg1 END;
22/

sql> CREATE OR REPLACE PROCEDURE proc3
2 is
3 BEGIN
4 pkg1.proc2;
5 END;
6/

Sql> BEGIN
2 proc3;
3 EXCEPTION
4 when OTHERS
5 Then
6 Dbms_output.put_line (backtrace_to);
7 END;
8/

HR. PKG1 Line 19

Note that the depth value in the error backtracking stack differs from the depth value of the call stack. For the call stack, 1 is the top of the stack (the currently executing subroutine). For the error backtracking stack, my code error is found using error_depth instead of 1.

With Utl_call_stack, I no longer need to parse the full backtracking string and use Dbms_utility. Format_error_backtrace had to do it. Instead, I can pinpoint, display and record the critical information I need.

There are a few things to keep in mind about Utl_call_stack:

The compiler's optimizations may change the depth of vocabulary, dynamics, and backtracking, because the optimization process may mean that subroutine calls are skipped.

If the boundary of the remote call is crossed, Utl_call_stack is not supported. For example, if PROC1 calls a remote procedure remoteproc2, REMOTEPROC2 will not get PROC1-related information using Utl_call_stack.

The information of the lexical unit is not obtained through utl_call_stack. You can use PL/SQL conditional compilation to get that information.

Utl_call_stack is a very handy tool, but in the real world, you might need to build some of your own tool code outside of the package's subroutines. I created a help pack with some tools inside and I think you might find it useful. You can find the code in the 12c_utl_call_stack_helper.sql and 12c_utl_call_stack_helper_demo.sql files.

Http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2041787.zip

Better diagnostics, better programming

Three dbms_utility functions (dbms_utility. Format_call_stack, Dbms_utility. Format_error_stack, and Dbms_utility. Format_error_ backtrace) has always been a good helper for diagnosing and resolving problems in PL/SQL code. Recognizing the importance of this data, the Utl_call_stack package is a big step forward, giving PL/SQL developers access to more deep, useful information.

"Go" oralce PL/SQL stack information tracking