Format Error Backtrace Oracle
Contents |
TECHNOLOGY: PL/SQL Tracing Lines By Steven Feuerstein Find and report your errors—by line number—in Oracle Database 10g. PL/SQL offers a powerful and flexible how to find which line error was raised in oracle exception architecture. Of course, there is always room for improvement, and dbms_utility.format_call_stack example in Oracle Database 10g, exception handling takes a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. format_error_stack vs format_error_backtrace This article explores the problem that this function solves and how best to use it. Who Raised That Exception? When an exception is raised, one of the most
Oracle Call Stack Trace
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 in Oracle9i Database. Consider this simple chain of program calls in Listing dbms_utility.format_error_backtrace 11g 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 raised on line 4 of proc1. On the one hand, we should be very pleased with this behavior. Now that we have the line number,
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about pl sql call stack Stack Overflow the company Business Learn more about hiring developers or posting ads with
Dbms_utility.format_error_backtrace In Oracle Forms
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is
Utl_call_stack
a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Oracle PL/SQL: how to get the stack trace, package name and procedure name up http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html vote 16 down vote favorite 4 Sometimes the exception returns something like: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small". It's not so readable since it doesn't report neither the table, the column and the value it tried to write. it would be useful to get the current procedure name at the moment the Exception happened or is catched. How can I obtain that? oracle exception-handling plsql http://stackoverflow.com/questions/7032373/oracle-pl-sql-how-to-get-the-stack-trace-package-name-and-procedure-name stack-trace share|improve this question edited Aug 12 '11 at 2:50 APC 87.1k1384184 asked Aug 11 '11 at 20:26 Revious 1,749135494 add a comment| 4 Answers 4 active oldest votes up vote 25 down vote accepted You probably want DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function SQL> ed Wrote file afiedt.buf 1 create or replace procedure p1 2 is 3 begin 4 raise_application_error( -20001, 'Error 1', true ); 5* end; SQL> / Procedure created. SQL> create or replace procedure p2 2 as 3 begin 4 null; 5 p1; 6 end; 7 / Procedure created. SQL> begin 2 p2; 3 exception 4 when others then 5 dbms_output.put_line( dbms_utility.format_error_backtrace ); 6 end; 7 / ORA-06512: at "SCOTT.P1", line 4 ORA-06512: at "SCOTT.P2", line 5 ORA-06512: at line 2 PL/SQL procedure successfully completed. share|improve this answer edited Mar 19 '15 at 8:43 Krumia 5,73432544 answered Aug 11 '11 at 20:36 Justin Cave 159k14203250 add a comment| up vote 5 down vote Or you could use DBMS_DEBUG.PRINT_BACKTRACE share|improve this answer edited Mar 19 '15 at 8:41 Krumia 5,73432544 answered Aug 11 '11 at 20:38 mamboking 3,3161321 add a comment| up vote 5 down vote Or try DBMS_UTILITY.FORMAT_CALL_STACK share|improve this answer edited Mar 19 '15 at 8:43 Krumia 5,73432544 answered Oct 27 '14 at 13:30 xmedeko 1,99311836 add a c
FORMAT_ERROR_BACKTRACE. FORMAT_ERROR_BACKTRACE function return the error stack along the place where exception occurred where as FORMAT_ERROR_STACK just returns the error stack. Syntax http://boloracle.blogspot.com/2012/08/logging-errors-using-error-back-trace.html of these two functions is : DBMS_UTILITY.FORMAT_ERROR_BACKTRACE RETURN VARCHAR2; 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 http://mynotes-oracle.blogspot.com/2010/01/dbmssql-error-stack-call-stack-and.html PROC0(); END; 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; in oracle /*CALL WITHOUT ERROR BACK TRACE*/ BEGIN PROC4(); END; Output of this when I ran in Oracle 11gR2 HR schema: 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" format error backtrace *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 comes handy when your program unit calls a lot of functions and procedures and you capture any unhandled exception in when other section - to ensure where this unhandled exception occurred. Cheers, Vijay Posted by Vijayaragavan at 10:55:00 pm Email This BlogThis! Share to Twitter Share to Facebook Share to Pinterest Labels: Exception , PLSQL No comments : Post a Comment Newer Post Older Post Home Subscribe to: Post Comments ( Atom ) About Me Vijayaragavan I am a database developer working with Or
to trace calling of programsDBMS_UTILITY.FORMAT_ERROR_STACK - used to seeDBMS_UTILITY.FORMAT_ERROR_BACKTRACE - used to locate the original place of origin of errorSample programs-DROP TABLE error_log/CREATE TABLE error_log(ERROR_ID NUMBER(5) PRIMARY 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 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