Pl Sql Error Stack 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, and in Oracle Database 10g, how to find which line error was raised in oracle exception handling takes a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function.
Pl Sql Call Stack
This article explores the problem that this function solves and how best to use it. Who Raised That Exception? When an exception oracle call stack trace 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 format_error_stack vs format_error_backtrace 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 significantly lacking in the top-level proc3 procedure. If I run proc3 in SQL*Plus,
Utl_call_stack
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 logging). Here is the second version of proc3 : CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN my_putline ( DBMS_UT
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 dbms_utility backtrace 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 http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html Database 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 C
SQL Server PRODUCTSDiscussion & Resources Benchmark Factory Code Tester for Oracle SharePlex SQL Navigator http://www.toadworld.com/platforms/oracle/w/wiki/3591.dbms-utility-format-error-backtrace SQL Optimizer Spotlight Stat Toad Intelligence Central Toad Data Modeler Toad Data Point Toad Extension for Eclipse Toad for Hadoop Toad for IBM http://paranoid-engineering.blogspot.com/2008/07/oracle-exception-handling-stack-trace.html DB2 Toad for Oracle Toad for MySQL Toad for SQL Server Toad for SAP Solutions Toad MAC Edition Home » Platforms pl sql » 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 Docker Enterprise Content Management Systems (ECM) Enterprise Manager pl sql error 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_TRANSFORM DBMS_TTS DBMS_UTILITY Overview (PLS5G1) Programs (OTOC345) DBMS_UTILITY.ACTIVE_INSTANCES DBMS_UTILITY.ANALYZE_DATABASE DBMS_UTILITY.ANALYZE_PART_OBJECT DBMS_UTILITY.ANALYZE_SCHEMA DBMS_UTILITY.CANONICALIZE DBMS_UTILITY.COMMA_TO_TABLE DBMS_UTILITY.COMPILE_SCHEMA DBMS_UTILITY.CREATE_ALTER_TYPE_ERR... DBMS_UTILITY.CURRENT_INSTANCE DBMS_UT
time ago I thought that PHP was the worst, but well, things change. I've been searching for the source of a weird CLOB related bug in a big pile of PL/SQL sh.. mess for a couple of days till I got fed up and decided to find a way to get the stack trace or at least the last line of code where the error was triggered from. Would you believe that before Oracle 10g there was no normal way to get the trace? Here's some Daily WTF material from the official PL/SQL User's Guide and Reference. ----- WTF EXCERPT START -----Using Locator Variables to Identify Exception LocationsUsing one exception handler for a sequence of statements can mask the statement that caused an error:BEGIN SELECT ... SELECT ...EXCEPTION WHEN NO_DATA_FOUND THEN ... -- Which SELECT statement caused the error?END;Normally, this is not a problem. But, if the need arises, you can use a locator variable to track statement execution, as follows:DECLARE stmt INTEGER := 1; -- designates 1st SELECT statementBEGIN SELECT ... stmt := 2; -- designates 2nd SELECT statement SELECT ...EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO errors VALUES ('Error in statement ' || stmt);END;----- WTF EXCERPT END -----Yes, they even have a name for this. Locator Variables. Damn. I can't decide whether to laugh or to cry...On a good note, since Oracle 10g you can use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to get a string representation of stack trace with procedure names and code line numbers. They still forgot to add this into the "Handling PL/SQL Errors" section of their manual... So, here's how you get the stack trace:declare x number;begin x := 1 / 0; dbms_output.put_line(x);exceptionwhen others then dbms_output.put_line(SQLERRM); dbms_output.put_line(dbms_utility.format_error_backtrace);end;Output:ORA-01476: divisor is equal to zeroORA-06512: at line 5And it took them only 10 versions to implement. Posted by Tomas Varaneckas at 17:35 Labels: fun, oracle, tips, work 10 comments: Andrea11