How To Get Error Stack Oracle
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 how to find which line error was raised in oracle flexible exception architecture. Of course, there is always room for improvement, pl sql call stack and in Oracle Database 10g, exception handling takes a big step forward with the introduction of the oracle call stack trace DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it. Who Raised That Exception? When an exception is raised, one of
Format_error_stack Vs Format_error_backtrace
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 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 utl_call_stack 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, 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 behavio
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company what are the methods there in save exceptions in oracle Business Learn more about hiring developers or posting ads with us Stack Overflow Questions
Dbms_utility Backtrace
Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million
Dbms_utility.format_error_backtrace 11g
programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Oracle PL/SQL: how to get the stack trace, package name and procedure name up vote 16 down vote favorite 4 Sometimes http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html the exception returns something like: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small". It's not so readable since it doesn't report neither the table, the column and the value it tried to write. it would be useful to get the current procedure name at the moment the Exception happened or is catched. How can I obtain that? oracle exception-handling plsql stack-trace share|improve this question edited Aug 12 '11 at 2:50 APC http://stackoverflow.com/questions/7032373/oracle-pl-sql-how-to-get-the-stack-trace-package-name-and-procedure-name 87.2k1384184 asked Aug 11 '11 at 20:26 Revious 1,749135494 add a comment| 4 Answers 4 active oldest votes up vote 25 down vote accepted You probably want DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function SQL> ed Wrote file afiedt.buf 1 create or replace procedure p1 2 is 3 begin 4 raise_application_error( -20001, 'Error 1', true ); 5* end; SQL> / Procedure created. SQL> create or replace procedure p2 2 as 3 begin 4 null; 5 p1; 6 end; 7 / Procedure created. SQL> begin 2 p2; 3 exception 4 when others then 5 dbms_output.put_line( dbms_utility.format_error_backtrace ); 6 end; 7 / ORA-06512: at "SCOTT.P1", line 4 ORA-06512: at "SCOTT.P2", line 5 ORA-06512: at line 2 PL/SQL procedure successfully completed. share|improve this answer edited Mar 19 '15 at 8:43 Krumia 5,74432544 answered Aug 11 '11 at 20:36 Justin Cave 159k14203250 add a comment| up vote 5 down vote Or you could use DBMS_DEBUG.PRINT_BACKTRACE share|improve this answer edited Mar 19 '15 at 8:41 Krumia 5,74432544 answered Aug 11 '11 at 20:38 mamboking 3,3161321 add a comment| up vote 5 down vote Or try DBMS_UTILITY.FORMAT_CALL_STACK share|improve this answer edited Mar 19 '15 at 8:43 Krumia 5,74432544 answered Oct 27 '14 at 13:30 xmedeko 1,99511836 add a comment| up vote 0 down vote I use the combination of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. (Improving the answer of Justin Cave) when others
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 http://blog.tanelpoder.com/2010/02/14/oracle-troubleshooting-how-to-read-oracle-errorstack-output/ 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 how to 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, how to get 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 v