Pl/sql Show 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 how to find which line error was raised in oracle for improvement, and in Oracle Database 10g, exception handling takes a big step pl sql call stack forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best dbms_utility.format_error_backtrace example in oracle to 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 oracle call stack trace 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 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
Oracle Pl Sql Error Line Number
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, 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 p
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux Home » Articles »
Format_error_stack Vs Format_error_backtrace
12c » Here UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in utl_call_stack Oracle Database 12c Release 1 (12.1) Oracle database 12c introduced the UTL_CALL_STACK package to allow programmatic access to the call pl sql line number stack and error stack, giving much greater flexibility for debugging and error handling of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html existing functionality in the DBMS_UTILITY package is still available and has not been deprecated. Call Stack Error Stack Backtrace Call Stack The call stack allows you to identify exactly where you are in the currently running code, which includes information about nesting of subprogram calls. In previous releases this information was displayed using the DBMS_UTILITY.FORMAT_CALL_STACK function, as shown below. -- Procedure to display the call stack. CREATE OR REPLACE https://oracle-base.com/articles/12c/utl-call-stack-12cr1 PROCEDURE display_call_stack AS BEGIN DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack); DBMS_OUTPUT.put_line('***** Call Stack End *****'); END; / -- Test package to show a nested call. CREATE OR REPLACE PACKAGE test_pkg AS PROCEDURE proc_1; PROCEDURE proc_2; PROCEDURE proc_3; END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS PROCEDURE proc_1 AS BEGIN proc_2; END; PROCEDURE proc_2 AS BEGIN proc_3; END; PROCEDURE proc_3 AS BEGIN display_call_stack; END; END; / -- Run the test. SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Call Stack Start ***** ----- PL/SQL Call Stack ----- object line object handle number name 0xb6d4ac18 4 procedure TEST.DISPLAY_CALL_STACK 0xb6d14298 15 package body TEST.TEST_PKG 0xb6d14298 10 package body TEST.TEST_PKG 0xb6d14298 5 package body TEST.TEST_PKG 0xb99fe7c8 1 anonymous block ***** Call Stack End ***** PL/SQL procedure successfully completed. SQL> As you can see, the output from the DBMS_UTILITY.FORMAT_CALL_STACK function is rather ugly and we have no control over it, other than to manually parse it. The UTL_CALL_STACK package contains APIs to display the contents of the call stack in a more readable form. DYNAMIC_DEPTH : The number of subprograms on the call stack, starting at the current position in the call stack, to the initial call. LEXICAL_DEPTH : Lexical depth of the subprogram within the current call. UNIT_
SQL Server PRODUCTSDiscussion & Resources Benchmark Factory Code Tester for Oracle SharePlex SQL Navigator SQL Optimizer Spotlight Stat Toad Intelligence Central Toad Data Modeler Toad Data Point Toad Extension http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/09/20/pl-sql-101-three-ways-to-get-error-message-stack-in-pl-sql for Eclipse Toad for Hadoop Toad for IBM DB2 Toad for Oracle Toad for MySQL Toad for SQL Server Toad for SAP Solutions Toad MAC Edition Home » Platforms http://blog.tanelpoder.com/2010/02/14/oracle-troubleshooting-how-to-read-oracle-errorstack-output/ » Oracle » Oracle Blog » PL/SQL 101: Three ways to get error message/stack in PL/SQL PL/SQL 101: Three ways to get error message/stack in PL/SQL Oracle Community Join Overview pl sql Forum Blog Wiki Members Blog Options Print Comment RSS Feed Tweet Related Posts PL/SQL 101: Raising exceptions in PL/SQL by Steven Feuerstein on 8 Sep 2016 0 comments PL/SQL Error Messages and Codes Package (TRGT1) latest revision over 3 years ago by Steve Hilker Error Messages latest revision over 3 years ago by Steve Hilker View More PL/SQL 101: Three pl/sql show error ways to get error message/stack in PL/SQL Follow Steven Feuerstein / 9.20.2016 at 2:54pm The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks.The three ways are:SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikelyto run into: the error stack will be truncated at 512 bytes, and you might lose some error information.DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will.UTL_CALL_STACK API - Added in Oracle Database 11g, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.Note: check out this LiveSQL script if you'd like to explore more of the nuances between SQLERRM and DBMS_UTILITY.FORMAT_ERROR_ST
usage on Solaris New versions of LatchProf and LatchProfX for latch contention troubleshooting and tuning → Oracle Troubleshooting: How to read Oracle ERRORSTACK output?! by Tanel Poder Posted on February 14, 2010 I have written the first article to the troubleshooting section of my new website tech.E2SN.com: It's about a very valuable Oracle troubleshooting tool -> ERRORSTACK trace. I cover 4 frequently asked questions there: Reading the current executing SQL statement text from errorstack trace Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace Reading the current bind variable values from errostack trace Identifying how much private (UGA) memory a cursor is using You can read it here: http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output By the way, if you like my new website, feel free to link to it !!! ;-) http://tech.e2sn.com NB! If you want to move to the "New World" - offload your data and workloads to Hadoop, without having to re-write your existing applications - check out Gluent. We are making history! ;-) Related PostsSqlplus is my second home: Part 6 - Colorizing sqlplus and terminal outputAdvanced Oracle Troubleshooting v2.5 (with 12c stuff too)Oracle Performance & Troubleshooting Online Seminars in 2013Session Snapper v4 - The World's Most Advanced Oracle Troubleshooting Script!Troubleshooting Another Complex Performance Issue - Oracle direct path inserts and SEG$…Share this post:Click to share on Twitter (Opens in new window)Click to share on Facebook (Opens in new window)Click to share on LinkedIn (Opens in new window)Click to share on Google+ (Opens in new window)MoreClick to print (Opens in new window)Click to share on Reddit (Opens in new window)Click to share on Pocket (Opens in new window)Click to share on Pinterest (Opens in new window)Click to share on Tumblr (Opens in new window)Click to email this to a friend (Opens in new window) This entry was posted in Cool stuff, Oracle and tagged Administration, Internals, Troubleshooting, Tuning. Bookmark the permalink. ← Using Process Memory Matrix script for calculating Oracle process memory usage on Solaris New versions of LatchProf and LatchProfX for latch contention troubleshooting and tuning → 18 Responses to Oracle Troubleshooting: How to read Oracle ERRORSTACK output?! Martin Decker says: February 15, 2010 at 3:41 am Hi Tanel, great article. Just a small typo: it´s v$sql_bind_capture instead of v$sql_plan_capture. Looking forward to reading more great stuff on e2sn… Regards, Martin Reply Tanel Po