Format Error Backtrace Example
Contents |
TECHNOLOGY: PL/SQL Tracing Lines By Steven Feuerstein Find and report your errors—by line number—in Oracle Database 10g. PL/SQL offers
Dbms_utility.format_call_stack Example
a powerful and flexible exception architecture. Of course, there is always how to find which line error was raised in oracle room for improvement, and in Oracle Database 10g, exception handling takes a big step forward with
Dbms_utility Backtrace
the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it. Who Raised That Exception? When format_error_stack vs format_error_backtrace an exception is raised, one of the most important pieces of information a programmer would like to uncover is the line of code that raised the exception. Prior to Oracle Database 10g, one could obtain this information only by allowing the exception to go unhandled. Let's revisit the error-handling behavior available to programmers dbms_utility.format_error_backtrace 11g in Oracle9i Database. Consider this simple chain of program calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at which point proc1 raises the NO_DATA_FOUND exception. Notice that there is no error handling in any of the procedures; it is most significantly lacking in the top-level proc3 procedure. If I run proc3 in SQL*Plus, I will see the following results: ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.PROC1", line 4 ORA-06512: at "SCOTT.PROC2", line 6 ORA-06512: at "SCOTT.PROC3", line 4 ORA-06512: at line 3 Code Listing 1: A stack of procedures CREATE OR REPLACE PROCEDURE proc1 IS BEGIN DBMS_OUTPUT.put_line ('running proc1'); RAISE NO_DATA_FOUND; END; / CREATE OR REPLACE PROCEDURE proc2 IS l_str VARCHAR2(30) := 'calling proc1'; BEGIN DBMS_OUTPUT.put_line (l_str); proc1; END; / CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; END; / This is the error trace dump of an unhandled exception, and it shows that the error was r
FORMAT_ERROR_BACKTRACE. FORMAT_ERROR_BACKTRACE function return the error stack along
Dbms_utility 12c
the place where exception occurred where as FORMAT_ERROR_STACK just dbms_utility.format_error_backtrace in oracle forms returns the error stack. Syntax of these two functions is : DBMS_UTILITY.FORMAT_ERROR_BACKTRACE RETURN VARCHAR2;
Pl Sql Call Stack
DBMS_UTILITY.FORMAT_ERROR_STACK RETURN VARCHAR2; Example: Create or replace procedure proc0 is Begin RAISE NO_DATA_FOUND; END; CREATE OR REPLACE PROCEDURE PROC1 IS BEGIN PROC0(); END; http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html CREATE OR REPLACE PROCEDURE PROC2 IS BEGIN PROC1(); END; CREATE OR REPLACE PROCEDURE PROC3 IS BEGIN PROC2(); END; CREATE OR REPLACE PROCEDURE PROC4 IS BEGIN PROC3(); END; /*CALL WITHOUT ERROR BACK TRACE*/ BEGIN PROC4(); END; Output of this when I ran in Oracle 11gR2 HR schema: http://boloracle.blogspot.com/2012/08/logging-errors-using-error-back-trace.html Error report: ORA-01403: no data found ORA-06512: at "HR.PROC0", line 3 ORA-06512: at "HR.PROC1", line 3 ORA-06512: at "HR.PROC2", line 3 ORA-06512: at "HR.PROC3", line 3 ORA-06512: at "HR.PROC4", line 3 ORA-06512: at line 2 01403. 00000 - "no data found" *Cause: *Action: Using Error back trace function, one can redirect these same error message to the logging mechanism they use for their application. In the below example I redirect the same into dbms_output. /*CALL WITH ERROR BACK TRACE*/ SET SERVEROUTPUT ON; BEGIN PROC4(); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------'); DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK); DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------'); END; anonymous block completed ------------------------------------------- ORA-01403: no data found ORA-06512: at "HR.PROC0", line 3 ORA-06512: at "HR.PROC1", line 3 ORA-06512: at "HR.PROC2", line 3 ORA-06512: at "HR.PROC3", line 3 ORA-06512: at "HR.PROC4", line 3 ORA-06512: at line 2 ------------------------------------------- These functions especially FORMAT_ERROR_BACKTRACE c
to trace calling of programsDBMS_UTILITY.FORMAT_ERROR_STACK - used to seeDBMS_UTILITY.FORMAT_ERROR_BACKTRACE - used to http://www.oracle-developer.net/display.php?id=318 locate the original place of origin of errorSample programs-DROP TABLE error_log/CREATE TABLE error_log(ERROR_ID NUMBER(5) PRIMARY in oracle KEY,PKG_NAME VARCHAR2(30),PROC_NAME VARCHAR2(30),CALL_STACK VARCHAR2(4000),ERROR_STACK VARCHAR2(4000),ERROR_BKTRC VARCHAR2(4000))/DROP SEQUENCE seq_error_log/CREATE SEQUENCE seq_error_log MINVALUE 1 START WITH 1 INCREMENT BY 1/CREATE OR REPLACE PROCEDURE p1ISlv_PKG_NAME VARCHAR2(30) := '';lv_PROC_NAME VARCHAR2(30) := 'p1';lv_CALL_STACK VARCHAR2(4000);lv_ERROR_STACK VARCHAR2(4000);lv_ERROR_BKTRC VARCHAR2(4000);BEGINDBMS_OUTPUT.put_line ('in format error backtrace p1, raising error');RAISE VALUE_ERROR;EXCEPTIONWHEN OTHERS THENlv_CALL_STACK := DBMS_UTILITY.FORMAT_CALL_STACK;lv_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;lv_ERROR_BKTRC := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;INSERT INTO error_logVALUES(seq_error_log.NEXTVAL,lv_PKG_NAME,lv_PROC_NAME,lv_CALL_STACK,lv_ERROR_STACK,lv_ERROR_BKTRC);COMMIT;END;/CREATE OR REPLACE PROCEDURE p2ISlv_PKG_NAME VARCHAR2(30) := '';lv_PROC_NAME VARCHAR2(30) := 'p2';lv_CALL_STACK VARCHAR2(4000);lv_ERROR_STACK VARCHAR2(4000);lv_ERROR_BKTRC VARCHAR2(4000);BEGINDBMS_OUTPUT.put_line ('in p2');DBMS_OUTPUT.put_line ('calling p1');p1;EXCEPTIONWHEN OTHERS THENlv_CALL_STACK := DBMS_UTILITY.FORMAT_CALL_STACK;lv_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;lv_ERROR_BKTRC := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;INSERT INTO error_logVALUES(seq_error_log.NEXTVAL,lv_PKG_NAME,lv_PROC_NAME,lv_CALL_STACK,lv_ERROR_STACK,lv_ERROR_BKTRC);COMMIT;END;/CREATE OR REPLACE PROCEDURE p3ISlv_PKG_NAME VARCHAR2(30) := '';lv_PROC_NAME VARCHAR2(30) := 'p3';lv_CALL_STACK VARCHAR2(4000);lv_ERROR_STACK VARCHAR2(4000);lv_ERROR_BKTRC VARCHAR2(4000);BEGINDBMS_OUTPUT.put_line ('in p3, calling p2');p2;EXCEPTIONWHEN OTHERS THENlv_CALL_STACK := DBMS_UTILITY.FORMAT_CALL_STACK;lv_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;lv_ERROR_BKTRC := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;INSERT INTO error_logVALUES(seq_error_log.NEXTVAL,lv_PKG_NAME,lv_PROC_NAME,lv_CALL_STACK,lv_ERROR_STACK,lv_ERROR_BKTRC);COMMIT;RAISE;END;/show errors/BEGINP3;END;/SELECT * FROM error_log/Referrences:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:433029981484http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#i1003874 Posted by Mukesh at 12:55 AM 1 comment: SenthilOctober 20, 2015 at 1:23 AMNice one bro.ReplyDeleteAdd commentLoad more... Newer P
Pipelines. It has always been possible in PL/SQL to identify the source of an exception in a block of code; i.e. to know the precise point at which a block of code failed. However, until Oracle 10g, it has not been possible for developers to intervene in the exception in any way whilst retaining this information (for example to record the exception and its source in application logs). This has been the cause of many a frustration for developers. Finally, with the release of 10g, Oracle has added provision for PL/SQL developers to trap AND log exceptions accurately for the first time. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function gives us the flexibility we have been demanding for years and the information that the DBMS_UTILITY.FORMAT_ERROR_STACK function simply didn't provide. The following simple PL/SQL block demonstrates that Oracle will happily tell us where a procedure, function or anonymous block hit an exception. SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 END; 4 / BEGIN * ERROR at line 1: ORA-00900: invalid SQL statement ORA-06512: at line 2 There are many PL/SQL developers who consider this to be adequate. They might, for example, take screen scrapes of their scheduling systems' output as application logs and be satisfied with the level of information demonstrated above. Or perhaps their front-end applications display the error stack as seen above. Many systems, however, have a requirement to write application logs to files or tables. Therefore, to ensure that the exception is logged, the following "pseudo-approach" is taken by many developers (note that in the simple examples that follow I've substituted DBMS_OUTPUT.PUT_LINE for an application logging package). SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( SQLERRM ); 6 RAISE; 7 END; 8 / ORA-00900: invalid SQL statement BEGIN * ERROR at line 1: ORA-00900: invalid SQL statement ORA-06512: at line 6 The result is that the point in the code where the exception was raised moves to the explicit RAISE call, as