Format Error Backtrace
Contents |
Oracle SQL Server PRODUCTSDiscussion & Resources Benchmark Factory Code Tester for Oracle how to find which line error was raised in oracle SharePlex SQL Navigator SQL Optimizer Spotlight Stat Toad Intelligence
Dbms_utility.format_call_stack Example
Central Toad Data Modeler Toad Data Point Toad Extension for Eclipse Toad for dbms_utility.format_error_backtrace 11g Hadoop Toad for IBM DB2 Toad for Oracle Toad for MySQL Toad for SQL Server Toad for SAP Solutions Toad MAC
Format_error_stack Vs Format_error_backtrace
Edition Home » Platforms » Oracle » Oracle Wiki » DBMS_UTILITY.FORMAT_ERROR_BACKTRACE DBMS_UTILITY.FORMAT_ERROR_BACKTRACE Oracle Community Join Overview Forum Blog Wiki Members Contents Oracle Wiki Oracle - Wiki Agile Development / DevOps Amazon APEX Big Data SQL Data Replication Database Administration Database Tuning dbms_utility 12c Docker Enterprise Content Management Systems (ECM) Enterprise Manager Exadata EXPLAIN PLAN Network Management NoSQL & Hadoop Oracle Applications Oracle Architecture Oracle Cloud Oracle Clusterware Oracle Development Oracle R Enterprise Oracle12c Peoplesoft PL/SQL Development Scripting Languages SQL Reference ANSI Reserved Words Built-in Packages DBMS_ADDM DBMS_ALERT DBMS_APPLICATION_INFO DBMS_APPLY_ADM DBMS_AQ DBMS_AQADM DBMS_AQELM DBMS_ASSERT DBMS_AUTO_TASK_ADMIN DBMS_CAPTURE_ADM DBMS_COMPARISON (OTOC197) DBMS_CUBE DBMS_CUBE_ADVISE DBMS_DB_VERSION DBMS_DDL DBMS_DEBUG DBMS_DEFER DBMS_DEFER_QUERY DBMS_DEFER_SYS DBMS_DESCRIBE DBMS_ERRLOG DBMS_EXPORT DBMS_FGA DBMS_FLASHBACK DBMS_IOT DBMS_JOB DBMS_LOB DBMS_LOCK DBMS_LOGMNR DBMS_LOGMNR_CDC_PUBLISH DBMS_LOGMNR_CDC_SUBSCRIBE DBMS_LOGMNR_D DBMS_LOGSTBY DBMS_METADATA DBMS_MVIEW DBMS_OBFUSCATION_TOOLKIT DBMS_OFFLINE_OG DBMS_OFFLINE_SNAPSHOT DBMS_OLAP DBMS_ORACLE_TRACE_AGENT DBMS_ORACLE_TRACE_USER DBMS_OUTLN DBMS_OUTPUT DBMS_PCLXUTIL DBMS_PIPE DBMS_PREPROCESSOR DBMS_PROFILER DBMS_RANDOM DBMS_RECTIFIER_DIFF DBMS_REDEFINITION DBMS_REFRESH DBMS_REPAIR DBMS_REPCAT DBMS_REPCAT_ADMIN DBMS_REPCAT_AUTH DBMS_REPCAT_INSTANTIATE DBMS_REPCAT_RGT DBMS_REPUTIL DBMS_RESOURCE_MANAGER DBMS_RESOURCE_MANAGER_PRIVS DBMS_RESULT_CACHE DBMS_RESUMABLE DBMS_RLS DBMS_ROWID DBMS_RULE DBMS_RULE_ADM DBMS_SESSION DBMS_SHARED_POOL DBMS_SNAPSHOT DBMS_SPACE DBMS_SPACE_ADMIN DBMS_SPM DBMS_SQL DBMS_STANDARD DBMS_STATS DBMS_STORAGE_MAP DBMS_STREAMS DBMS_STREAMS_ADM DBMS_STREAMS_ADVISOR_ADM DBMS_SYSTEM DBMS_TRACE DBMS_TRANSACTION DBMS_TRANS
to trace calling of programsDBMS_UTILITY.FORMAT_ERROR_STACK - used utl_call_stack to seeDBMS_UTILITY.FORMAT_ERROR_BACKTRACE - used to locate the original place of origin of errorSample programs-DROP http://www.toadworld.com/platforms/oracle/w/wiki/3591.dbms-utility-format-error-backtrace 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 http://mynotes-oracle.blogspot.com/2010/01/dbmssql-error-stack-call-stack-and.html 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 ('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_QUESTI
FORMAT_ERROR_BACKTRACE. FORMAT_ERROR_BACKTRACE function return the error stack along the place where exception occurred where as FORMAT_ERROR_STACK just returns http://boloracle.blogspot.com/2012/08/logging-errors-using-error-back-trace.html the error stack. Syntax 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 https://dzone.com/articles/plsql-backtraces-debugging OR REPLACE PROCEDURE PROC1 IS BEGIN PROC0(); END; CREATE OR REPLACE PROCEDURE PROC2 IS BEGIN PROC1(); END; CREATE OR REPLACE PROCEDURE PROC3 IS BEGIN PROC2(); END; CREATE OR in oracle 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: 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 format error backtrace 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 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
Guides Zones | Agile Big Data Cloud Database DevOps Integration IoT Java Mobile Performance Web Dev Let's be friends: PL/SQL Backtraces for Debugging by Lukas Eder · Sep. 01, 14 · Java Zone Like (0) Comment (0) Save Tweet {{ articles[0].views | formatCount}} Views Edit Delete {{ articles[0].isLocked ? 'Enable' : 'Disable' }} comments {{ articles[0].isLimited ? 'Remove comment limits' : 'Enable moderated comments' }} Join the DZone community and get the full member experience. Join For Free Learn more about the advantages of moving from a monolithic to microservices architecture. Brought to you in partnership with IBM. For many PL/SQL developers, this might be common sense, but for one of our customers, this was an unknown PL/SQL feature: Backtraces. When your application raises an error somewhere deep down in the call stack, you don’t get immediate information about the exact source of the error. For large PL/SQL applications, this can be a pain. One workaround is to keep track of statement numbers that were last executed before any error occurred:DECLARE v_statement_no := 0; BEGIN v_statement_no := 1; SELECT ... v_statement_no := 2; INSERT ... v_statement_no := 3; ... EXCEPTION WHEN OTHERS THEN -- Log error message somewhere logger.error(module, v_statement_no, sqlerrm); END;The above looks an awful lot like println-debugging, a thing that isn’t really known to Java developers ;-) But println-debugging isn’t necessary in PL/SQL either. Use the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, instead! An example:DECLARE PROCEDURE p4 IS BEGIN raise_application_error(-20000, 'Some Error'); END p4; PROCEDURE p3 IS BEGIN p4; END p3; PROCEDURE p2 IS BEGIN p3; END p2; PROCEDURE p1 IS BEGIN p2; END p1; BEGIN p1; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(sqlerrm); dbms_output.put_line( dbms_utility.format_error_backtrace ); END; /The above PL/SQL block generates the following output:You can see exactly what line number generated the error. If you’re not using local procedures in anonymous blocks (which you quite likely aren’t), this gets even more