Support For dbms_utility.format_error_backtrace

Availability

This feature is available since MogDB 5.0.0.

Introduction

dbms_utility.format_error_backtrace is used to get the exact source of a procedure exception, outputting a formatted string stack of the program and its line number back to the line where the error was first thrown. Therefore, this interface can only be called on procedure exceptions.

This interface is implemented in the whale plugin, so it is a prerequisite that the whale plugin has been created first. If you need to print out the stack information, you need to use dbms_output.put_line.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Constraints

dbms_utility.format_error_backtrace can only be used in the exception section of a procedure procedure and does not support functions.
If you need to print the contents of dbms_utility.format_error_backtrace, you need to use the dbms_output.put_line interface.

The following SQL statement needs to be executed before using the dbms_output.put_line interface:
```
select dbms_output.enable(***)；
-- Enable the dbms_output package.

set serveroutput to on;
-- Support for printing data
```
The number of lines printed by format_error_backtrace is the number of lines of the stored procedure at the level where it is located, not the number of lines of all the expanded stored procedures.
Support for inserting format_error_backtrace as a default value in the procedure

Example

-- The whale extension needs to be created first

MogDB=# set whale.serveroutput to on;
SET

MogDB=# select dbms_output.enable(10000);
 enable
--------

(1 row)

-- dbms_utility.format_error_backtrace record exception stack information

MogDB=# Create or replace procedure proc1 is
MogDB$# Begin
MogDB$# Dbms_output.put_line('running proc1');
MogDB$# Raise no_data_found;
MogDB$# End;
MogDB$# /
CREATE PROCEDURE
MogDB=# create or replace procedure proc2 is
MogDB$# begin
MogDB$# dbms_output.put_line('calling proc1');
MogDB$# dbms_output.put_line('---------------');
MogDB$# proc1;
MogDB$# end;
MogDB$# /
CREATE PROCEDURE
MogDB=#
MogDB=# create or replace procedure proc3 is
MogDB$# begin
MogDB$# dbms_output.put_line('calling proc2');
MogDB$# proc2;
MogDB$# exception
MogDB$# when no_data_found
MogDB$# then
MogDB$# dbms_output.put_line('error stack at top level');
MogDB$# dbms_output.put_line(dbms_utility.format_error_backtrace);
MogDB$# end;
MogDB$# /
CREATE PROCEDURE

MogDB=# begin
MogDB$# dbms_output.put_line('proc3->proc2->proc1 backtrace');
MogDB$# proc3;
MogDB$# end;
MogDB$# /
proc3->proc2->proc1 backtrace
calling proc2
calling proc1
---------------
running proc1
error stack at top level
P0002: at "public.proc1", line 4
P0002: at "public.proc2", line 5
P0002: at "public.proc3", line 4

ANONYMOUS BLOCK EXECUTE
MogDB=#

-- dbms_utility.format_error_backtrace can be used as a parameter default

MogDB=# create or replace  procedure proc_test_a(i_err_bt varchar2 default dbms_utility.format_error_backtrace,i_sqlerm varchar2 default 'ddd') is
MogDB$# begin
MogDB$#   dbms_output.put_line('proc_test_a');
MogDB$#   dbms_output.put_line(i_err_bt ||i_sqlerm);
MogDB$# end;
MogDB$# /
CREATE PROCEDURE
MogDB=#

MogDB=# declare
MogDB-#   a int;
MogDB-# begin
MogDB$#   a := 'abc';
MogDB$# exception
MogDB$#   when others then
MogDB$#      proc_test_a;
MogDB$# end;
MogDB$# /
proc_test_a
ddd
ANONYMOUS BLOCK EXECUTE
MogDB=#