Printing Oracle Error Stack
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
Dbms_utility.format_error_backtrace Example In Oracle
improvement, and in Oracle Database 10g, exception handling takes a big step forward how to find which line error was raised in oracle with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to
Pl Sql Call Stack
use it. 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 oracle call stack trace 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 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 format_error_stack vs format_error_backtrace 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, 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 disp
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 Call
Utl_call_stack
Stack Analysis By Steven Feuerstein UTL_CALL_STACK package in Oracle Database 12c gives developers what are the methods there in save exceptions in oracle better answers. Answer to Previous Challenge The PL/SQL Challenge question in last issue’s “SQL in PL/SQL Enhancements” article focused
Dbms_utility.format_error_backtrace 11g
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 new PL/SQL features http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html 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 12c added UTL_CALL_STACK. http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html 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 ——————— object handle line number object name 000007FF7EA83240
Oracle 11gR1 RAC to 11gR2 Applying PSU 11.2.0.3.5 to Grid Infrastructure and DB Home ASM Dataguard Install Transportable Tablespace Example Oracle12c Performance Grid MySQL Reset MySQL Root Password Unix Linux AIX HP-UX Solaris Netapp Reference Resource Center ADRCI Starting with Oracle Goldengate Scripts http://askdba.org/weblog/2008/04/errorstack-oracle-debugging-event/ Useful SQL for DBA's Useful Metalink Articles About Us Contact ERRORSTACK- Oracle Debugging Event http://www.oracle-developer.net/display.php?id=318 Amit Bansal / 17 April, 2008 One more post arising out of my frequent visits to Oracle forum 😉 Basically there was a question on "How to set events for ORA - 1652 errors" I had replied to the post but Aman asked me if I could provide any notes regarding Errorstack.I didn't had one so I informed him that in oracle I will be posting on my blog ( Cheap Publicity of my blog 🙂 ) Ok, this was the background to it, now coming to the main thing. Errorstack is used as debugging event for following reasons To extract Failing SQL statement To get Oracle Function call (to be used by Oracle) for interpreting cause for the error (e.g ORA 4030, ORA- 4031). It is also used in case of database hang scenarios. Refer to printing oracle error my earlier article on database hang It also contains execution plan in some cases which can be used to diagnose ORA - 1652 errors (Excessive temp usage) Errorstack can be set at System level and also at Session level. e.g To set errorstack for ORA - 1652 ,Syntax will be alter system set events '1652 trace name errorstack level 3'; If suppose, you want to set it at session level, then alter session set events '1652 trace name errorstack level 3'; You can also use oradebug to take errorstack for particular process (say ospid 1234) as below sqlplus "/ as sysdba" oradebug setospid 1234 oradebug unlimit oradebug dump errorstack 3 There are different levels for the errorstack with level 3 being the highest. Note: There is no such level as level 10 or 12 which you might sometimes find on web. e.g event='1401 trace name errorstack, level 12' Extracted from http://www.orafaq.com/faqdbain.htm . Specifying this will default to level 3. Let's see one trace file for error ORA - 942 i.e "table or view does not exist" SYS>alter session set events '942 trace name errorstack level 3'; Session altered. SYS>select * from err; select * from err *ERROR at line 1:ORA-00942: table or view does not exist Trace file is generated in user_dump_dest as a user process has failed. Trace file Header /u01/app/oracle/admin/sql10g/udump/sql10g_ora_23773.trc
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