Error Backtrace Oracle
Contents |
10g Release 2, is a critical subprogram to call when logging exceptions. It returns a string that traces how to find which line error was raised in oracle the error back to the line on which it was raised! dbms_utility backtrace Note: if you re-raise an exception as it propagates up the stack, you will lose the
Format_error_stack Vs Format_error_backtrace
"original" line number. The back trace function always only traces back to the most recently raised exception. Category PL/SQL General Contributor Steven Feuerstein (Oracle) Created Tuesday January
Oracle Pl Sql Error Line Number
26, 2016 Statement 1 CREATE OR REPLACE PROCEDURE proc1 IS BEGIN DBMS_OUTPUT.put_line ('running proc1'); RAISE NO_DATA_FOUND; END; Procedure created. Statement 2 CREATE OR REPLACE PROCEDURE proc2 IS l_str VARCHAR2 (30) := 'calling proc1'; BEGIN DBMS_OUTPUT.put_line (l_str); proc1; END; Procedure created. Statement 3 CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; END; Procedure dbms_utility.format_error_backtrace 11g created. Statement 4 The only way to "see" the line number on which the error was raised was to let the exception go unhandled.Without Back Trace....BEGIN DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 unhandled'); proc3; END; ORA-01403: no data found Statement 5 Sure, that works fine and is very good info to have, but the error stack (error message) will contain the line number on which the error was raised!Trap and Display Error Stack (Error Message)BEGIN DBMS_OUTPUT.put_line ('Proc3 -> Proc2 -> Proc1 unhandled'); proc3; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); END; Proc3 -> Proc2 -> Proc1 unhandledcalling proc2calling proc1running proc1Proc3 -> Proc2 -> Proc1 unhandledcalling proc2calling proc1running proc1ORA-01403: no data found Statement 6 Now we trap the exception at the top level subprogram and view both the error stack and the back trace.Add Back Trace to Error HandlerCREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error stack at top level:'); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrac
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
Pl Sql Call Stack
Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation dbms_utility.format_error_backtrace in oracle forms Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just what are the methods there in save exceptions in oracle 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 returns https://livesql.oracle.com/apex/livesql/file/content_CSBE3ZJTSXBTUEI174J84WGO0.html 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 87k1383184 asked Aug 11 http://stackoverflow.com/questions/7032373/oracle-pl-sql-how-to-get-the-stack-trace-package-name-and-procedure-name '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,67532544 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,67532544 answered Aug 11 '11 at 20:38 mamboking 3,3061321 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,67532544 answered Oct 27 '14 at 13:30 xmedeko 2,00311836 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_line ( DBMS_UTILITY.FORMAT_ERROR
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux Home » Articles » 12c » Here UTL_CALL_STACK https://oracle-base.com/articles/12c/utl-call-stack-12cr1 : 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 http://www.dba-oracle.com/t_ora_06512_tips.htm 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 existing functionality in the DBMS_UTILITY package is in oracle 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 PROCEDURE display_call_stack AS BEGIN DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack); error backtrace oracle 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_LINE : Line number in the subprogram of the current call. SUBPROGRAM : Subprogram name associated with the current call. CONCATENATE_SUBPROGR
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ORA-06512 Tips Oracle Error Tips by Burleson Oracle docs note this about Oracle ORA-06512: ORA-06512: at stringline string. Cause: Backtrace message as the stack is unwound by unhandled exceptions.Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA. Oracle ORA-06512 is the catch-all Oracle error for PL/SQL exceptions. Oracle ORA-06512 will generally appear as part of a message stack in which the message directly preceding Oracle ORA-06512 will list the reason for the error.For example, this user finds himself being thrown Oracle ORA-06512 and ORA-04088 while attempting to create a custom trigger as follows: 1 CREATE OR REPLACE TRIGGER hari_dt 2 BEFORE INSERT 3 ON 4 hari 5 FOR EACH ROW 6 DECLARE 7 v_name varchar2(20); 8 BEGIN 9 v_name :=upper(:new.name); 10 insert into hari values(v_name); 11* end; 12 / Trigger created. SQL> insert into hari values('harindar'); insert into hari values('harindar') * ERROR at line 1: ORA-00036: Maximum number of recursive sql levels (50) exceeded ORA-06512: at "BUSMASTERQ.HARI_DT", line 5 ORA-04088: error during execution of trigger 'BUSMASTERQ.HARI_DT' ORA-06512: at "BUSMASTERQ.HARI_DT", line 5 ORA-04088: error during execution of trigger 'BUSMASTERQ.HARI_DT' ORA-06512: at "BUSMASTERQ.HARI_DT", line 5 ORA-04088: error during execution of trigger 'BUSMASTERQ.HARI_DT' ORA-06512: at "BUSMASTERQ.HARI_DT", line 5 ORA-04088: error during execution of trigger 'BUSMASTERQ.HARI_DT' ORA-06512: at "BUSMASTERQ.HARI_DT", line 5 ORA-04088: error during execution of trigger 'BUSMASTERQ.HARI_DT' ORA-06512: at "BUSMASTERQ.HARI_DT", line 5 ORA-04088: error during execution of trigger 'BUSMASTERQ.HARI_DT' ORA-06512: at "BUSMASTERQ.HARI_DT", line 5 ORA-04088: error during execution of trigger 'BUSMASTERQ.HARI_DT' ORA-06512: at "BUSMASTERQ.HARI_D