Oracle gets the exact source of the exception dbms_utility.format_error_backtrace

dbms_utility. Format_error_backtrace : Returns the description of the current exception, which allows you to know where the exception was originally generated.

The system sets a stack for the last generated exception and tracks its delivery, which uses the stack and returns the entire delivery process for that exception. This function plays an important role in locating errors and implementing the next step of processing.

Dbms_utility. Format_error_backtrace

The system sets a stack for the last generated exception and tracks its delivery, which uses the stack and returns the entire delivery process for that exception. This function plays an important role in locating errors and implementing the next step of processing.

Create or replace procedure Procl is

Begin

Dbms_output.put_line (' running Proc1 ');

Raise No_data_found;

End;

/

Create or replace procedure PROC2 is

Begin

Dbms_output.put_line (' calling Proc1 ');

Proc1;

End

/

Create or replace procedure PROC3 is

Begin

Dbms_output.put_line (' calling proc2 ');

PROC2;

exception

When No_data_found

Then

Dbms_output.put_line (' Error stack at top ');

Dbms_output.put_line (Dbms_utility.format_error_backtrace);

End

/

You can now run PROC3 to see the results.

Sql>set serveroutput on;

Sql>begin

2 Dbms_output.put_line (' Proc3->proc2->proc1 backtrace ');

3 proc3;

4 End;

5/

PROC3, PROC2, Proc1 backtrace

Calling Proc2

Calling Proc1

Running Proc1

Error stack at top level:

Ora-06512:at "SCOTT. PROC1 ", line 4

Ora-06512:at "SCOTT. PROC2 ", line 5

Ora-06512:at "SCOTT. PROC3 ", line 4

In fact, each time the exception is generated will reset the exception stack, but the last time the stack from the system stack is the outermost program block, so you can clearly see the entire process of the exception generation. Above this program execution process is this: first with put_line printing Proc3, Proc2, Proc1 backtrace, call PROC3, the current program into the stack and print calling PROC2, call proc 2, proc3 into the stack and print calling Proc1, call Proc1, proc2 into the stack and print running Proc1, generate No_data_found exception, the exception is pressed into the exception stack = proc 2 out of the stack, and detected from the 5th line call passed the exception, it is pressed into the exception stack and proc3 out of the stack, and detected from the 4th line call passed by the exception, it is pressed into the exception stack, dbms_utility.format_error_backtrace The information in the exception stack is reversed to print out and the outermost program out of the stack, end.

Here are some considerations for proper use of this function:

ü Call this function in the exception handling module of the current program.

ü Avoid using exception handling modules in intermediate programs.

The exception can be correctly transferred to the outermost program, and the process is printed out.

Oracle gets the exact source of the exception dbms_utility.format_error_backtrace