PL/SQL Smart Call stack analysis

PL/SQL Smart Call stack analysis
Original: http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html

The three dbms_utility functions
(Dbms_utility. Format_call_stack, Dbms_utility. Format_error_stack, and Dbms_utility. Format_error_ BackTrace) has been crucial aids in diagnosing and resolving problems in PL/SQL code. The Utl_call_stack package recognizes the importance of this data and takes a big step forward in giving PL/SQL developers Access to more in-depth and useful information
12C Previous 3 tool functions (dbms_utility. Format_call_stack,dbms_utility. Format_error_stack,dbms_utility. Format_error_ backtrace)
The critical Help for PL/SQL program analysis and problem resolution has been provided.
The Utl_call_stack package, introduced in 12C, is aware of the importance of the modified data and is further enhanced to enable PL/SQL developers to gain more in-depth, useful information.

– Call stack called stacks:dbms_utility. Format_call_stack
answered "How do I get here?" and how do I get here step-by-step? For example:

Sql>CREATE ORREPLACE PROCEDURE proc1 2  is 3 BEGIN 4 dbms_output. Put_Line (Dbms_utility.format_call_stack);  5  END;6/sql>CREATE ORREPLACE Package Pkg12   is  3      PROCEDURE proc2;  4  ENDPKG1;5/sql>CREATE ORREPLACE Package BODY Pkg12   is  3      PROCEDURE proc2 4  is 5 BEGIN 6 proc1;  7     END;8  ENDPKG1;9/sql>CREATE ORREPLACE PROCEDURE proc3 2  is 3 BEGIN 4  for indx in 1.. 5 LOOP 6 NULL;  7     END LOOP;8  9PKG1.PROC2;Ten  END; One/sql>BEGIN  2PROC3;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

Disadvantages
If you call a subprogram in a package, the formatted call stack would show only the package name, not the subprogram name a nd certainly not the names of nested subprograms defined within, that packaged subprogram.
If we call the subroutine in the package, then this function can only show the registration, not even the name of the subroutine, not to mention the name of the nested child program.

If you simply want the name of the most recently executed subprogram, you'll have the to parse the string. This isn't hard-to-do, but it's more code, which has to write and maintain.
If we just want to see the name of the recently executed subroutine, we have to parse the lengthy string. While this is not a difficult task, it certainly adds to the burden on developers.

The object handle value is, for all practical purposes, "noise." PL/SQL developers-outside of Oracle, at least-never use it.
Object handle value is a chicken, no practical use.

– Wrong stack error stacks:dbms_utility. Format_error_stack Similar to SQLERRM
The dbms_utility. Format_error_stack function differs from SQLERRM in ways:

It can return an error message as long as 1,899 characters, thereby avoiding (or at least making extremely unlikely) trunc ation issues when the error stack gets a long. (SQLERRM truncates at only 510 characters.)

You cannot pass a error code number to this function, and it cannot is used to return the message for an error code.

– Error backtracking errors backtraces:dbms_utility. Format_error_backtrace
Returns a formatted string that displays a stack of programs and line numbers tracing back to the line on which the error was originally raised.

12c:utl_call_stack Package
Name Description
Backtrace_depth Returns The number of backtrace items in the BackTrace
Backtrace_line Returns The line number of the unit at the specified backtrace depth
Backtrace_unit Returns The name of the UNIT at the specified backtrace depth
Concatenate_subprogram Returns A concatenated form of a unit-qualified name
Dynamic_depth Returns The number of subprograms in the "Call stack", including SQL, Java, and other non-pl/sql contexts Invo Ked along the way-for example, if a calls B calls C calls B, this stack, written as A line with dynamic depths underneath It, would look like this:

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 of the error at the specified error depth
Error_number Returns The error number of the error at the specified error depth
Lexical_depth Returns The LEXICAL nesting level of the subprogram at the specified dynamic DEPTH
Owner Returns The owner name of the unit of the subprogram at the specified dynamic depth
Unit_line Returns The line number of the unit of the subprogram at the specified dynamic depth
Subprogram Returns The unit-qualified name of the subprogram at the specified dynamic depth

Sql>CREATE ORREPLACE PROCEDURE format_call_stack_12c 2  is 3 BEGIN 4 dbms_output. Put_Line (5 ' lexdepth Depth lineno Name ');  6Dbms_output.put_line (7        '-----------------------');8  9      forThe_depthinch REVERSE 1..TenUtl_call_stack.dynamic_depth () One     LOOP  ADbms_output.put_line ( -Rpad ( -Utl_call_stack.lexical_depth ( theThe_depth), -                 9) -|| Rpad (The_depth,5) -|| Rpad ( +To_char ( -Utl_call_stack.unit_line ( +The_depth), A                    ' the '), at                 8) -|| Utl_call_stack.concatenate_subprogram ( -Utl_call_stack.subprogram ( -(the_depth))); -     END LOOP; -  END; in/sql>CREATE ORREPLACE Package Pkg2   is  3      PROCEDURE do_stuff;  4  END;5/sql>CREATE ORREPLACE Package BODY Pkg2   is  3      PROCEDURE do_stuff 4  is 5 PROCEDURE np1 6 Is 7 PROCEDURE np2 8  is 9 PROCEDURE np3< /c13> is  a BEGIN format_call_stack_12c; /c2>  -              END; -           BEGIN  theNP3; -           END; -        BEGIN  -NP2; +        END; -     BEGIN  +NP1; A     END; at  END; -/sql>BEGIN  2Pkg.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

Sql>CREATE ORREPLACE 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);  A  END; -/sql>CREATE ORREPLACE Package Pkg12   is  3      PROCEDURE proc1;  4      PROCEDURE proc2;  5  END;6/sql>CREATE ORREPLACE Package BODY Pkg12   is  3      PROCEDURE proc1 4  is 5 PROCEDURE nested_in_proc1 6 C8>is 7 BEGIN 8 RAISE value_error;  9        END;Ten     BEGIN  OneNested_in_proc1; A     END; -  -      PROCEDURE proc2 is  the BEGIN of proc1;  -EXCEPTION +When OTHERS Then RAISENo_data_found; -     END; +  ENDPKG1; A/sql>CREATE ORREPLACE PROCEDURE proc3 2  is 3 BEGIN 4 pkg1. proc2;  5  END;6/sql>BEGIN  2PROC3;3EXCEPTION4When OTHERS5      Then  6Dbms_output.put_line (backtrace_to);7  END;8/

HR. PKG1 Line 19

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

PL/SQL Smart Call stack analysis