Oracle Pl Sql Error Trace
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 exception architecture. Of course, there is always room for improvement,
Dbms_utility.format_error_backtrace Example In Oracle
and in Oracle Database 10g, exception handling takes a big step forward with the how to find which line error was raised in oracle introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it. pl sql call stack Who Raised That Exception? When 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 Call Stack Trace
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 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
Format_error_stack Vs Format_error_backtrace
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, we can zoom right in on the problem code and fix it. On the other hand, we got this information by letting the exception go unhandled. In many applications, however, we work to avoid unhandled exceptions. Let's see what happens when I add an exception section to the proc3 procedure and then display the error information (the simplest form of error log
January 2014 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 January 2014 March 2014 May 2014 July 2014 September 2014 November 2014 As Published In January/February 2014 TECHNOLOGY: PL/SQL Sophisticated utl_call_stack Call Stack Analysis By Steven Feuerstein UTL_CALL_STACK package in Oracle Database 12c
Dbms_utility.format_error_backtrace 11g
gives developers better answers. Answer to Previous Challenge The PL/SQL Challenge question in last issue’s “SQL in PL/SQL Enhancements” what are the methods there in save exceptions in oracle article focused on enhancements for executing SQL from PL/SQL in Oracle Database 12c. The quiz demonstrated the new DBMS_SQL .return_result procedure, and all three choices were correct! This third and last article on http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html new PL/SQL features in Oracle Database 12c Release 1 focuses on the new UTL_CALL_STACK package. Call Stacks, Error Stacks, and Error Backtraces Prior to Oracle Database 12c, Oracle Database offered several DBMS_UTILITY functions to answer key questions programmers ask as they develop, debug, and maintain their code, and the functions have been extremely helpful. There is, however, always room for improvement, and that is why Oracle Database http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html 12c added UTL_CALL_STACK. Before I dive into UTL_CALL_STACK, here’s a refresher on the three DBMS_UTILITY functions that are reimagined by the new UTL_CALL_STACK package. DBMS_UTILITY.FORMAT_CALL_STACK. Introduced in Oracle7, the DBMS_UTILITY.FORMAT_CALL_STACK built-in function returns a formatted string that shows the execution call stack: the sequence of invocations of procedures or functions that led to the point at which the function was called. In other words, this function answers the question, “How did I get here?” Listing 1 demonstrates the DBMS_UTILITY.FORMAT_CALL_STACK function and what the formatted string looks like. Code Listing 1: Demonstration of the DBMS_UTILITY.FORMAT_CALL_STACK function SQL> CREATE OR REPLACE PROCEDURE proc1 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); 5 END; 6 / SQL> CREATE OR REPLACE PACKAGE pkg1 2 IS 3 PROCEDURE proc2; 4 END pkg1; 5 / SQL> CREATE OR REPLACE PACKAGE BODY pkg1 2 IS 3 PROCEDURE proc2 4 IS 5 BEGIN 6 proc1; 7 END; 8 END pkg1; 9 / SQL> CREATE OR REPLACE PROCEDURE proc3 2 IS 3 BEGIN 4 FOR indx IN 1 .. 1000 5 LOOP 6 NULL; 7 END LOOP; 8 9 pkg1.proc2; 10 END; 11 / SQL> BEGIN 2 proc3; 3 END; 4 / ——————— PL/SQL Call Stack ——————&mdas
SQL Server PRODUCTSDiscussion & Resources Benchmark Factory Code Tester for http://www.toadworld.com/platforms/oracle/w/wiki/3591.dbms-utility-format-error-backtrace Oracle SharePlex SQL Navigator SQL Optimizer Spotlight Stat https://blog.jooq.org/2014/08/29/plsql-backtraces-for-debugging/ Toad Intelligence Central Toad Data Modeler Toad Data Point Toad Extension for Eclipse Toad for Hadoop Toad for IBM DB2 Toad for Oracle Toad for MySQL Toad for SQL Server Toad for SAP in oracle Solutions Toad MAC 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 oracle pl sql Administration Database Tuning 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_ST
backtraces fordebugging 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: ORA-20000: Some Error ORA-06512: at line 3 ORA-06512: at line 6 ORA-06512: at line 9 ORA-06512: at line 12 ORA-06512: at line 16 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 useful: CREATE PROCEDURE p4 IS BEGIN raise_application_error(-20000, 'Some Error'); END p4; / CREATE PROCEDURE p3 IS BEGIN p4; END p3; / CREATE PROCEDURE p2 IS BEGIN p3; END p2; / CREATE 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; /