Format Error Backtrace 9i
Contents |
to Oracle 10g one way to obtain this information by allowing the exception to go unhandled. test@9iR2> select * from
Dbms_utility.format_error_backtrace Example In Oracle
v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL how to find which line error was raised in oracle Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for 32-bit Windows: Version 9.2.0.8.0 - Production NLSRTL Version format_error_stack vs format_error_backtrace 9.2.0.8.0 - Production test@9iR2> create or replace procedure p1 as 2 begin 3 dbms_output.put_line ('raising error at p1'); 4 raise no_data_found; 5 end; 6
Oracle Call Stack Trace
/ Procedure created. test@9iR2> create or replace procedure p2 as 2 begin 3 p1; 4 end; 5 / Procedure created. test@9iR2> create or replace procedure p3 as 2 begin 3 p2; 4 end; 5 / Procedure created. test@9iR2> exec p3; raising error at p1 BEGIN p3; END; * ERROR at
Pl Sql Call Stack
line 1: ORA-01403: no data found ORA-06512: at "TEST.P1", line 4 ORA-06512: at "TEST.P2", line 3 ORA-06512: at "TEST.P3", line 3 ORA-06512: at line 1 Now we have the line number, we can zoom right into the code and fix it, on the other hand we got this information by letting the Exception going unhandled. How ever in many application we work to avoid unhandled exception. Let's see what happen when exception handled in procedure P3. test@9iR2> create or replace procedure p3 as 2 begin 3 p2; 4 exception 5 when others then 6 dbms_output.put_line (' calling format error stack from P3 '); 7 dbms_output.put_line (dbms_utility.format_error_stack); 8 end; 9 / Procedure created. Notice that I call dbms_utility.format_error_stack, it returns the full error message. test@PWTK52> exec p3; raising error at p1 calling format error stack from P3 ORA-01403: no data found PL/SQL procedure successfully completed. dbms_utility.format_error_stack - does not show the full error stack with line numbers. In Oracle database 10g, Oracle added format_error_backtr
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 Business utl_call_stack Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs
Dbms_utility.format_error_backtrace 11g
Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just utl_call_stack example 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 the exception http://raajeshwaran.blogspot.com/2010/05/dbmsutilityformaterrorbacktrace-10g-new.html 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 87.1k1384184 asked Aug http://stackoverflow.com/questions/7032373/oracle-pl-sql-how-to-get-the-stack-trace-package-name-and-procedure-name 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,73432544 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,73432544 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,73432544 answered Oct 27 '14 at 13:30 xmedeko 1,99311836 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 then Dbms_Output.put_line ( DBMS_UTILITY.FORMAT_ERROR_STACK() ); Dbms_Output.put_l
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | https://oracle-base.com/articles/12c/utl-call-stack-12cr1 SQL | RAC | WebLogic | Linux Home » Articles » 12c » Here UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release 1 (12.1) Oracle database 12c introduced the UTL_CALL_STACK package to allow programmatic access to the call stack and error stack, giving much greater flexibility for debugging and in oracle error handling of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The 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 format error backtrace 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 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