Oracle Get Error Stack Trace
Contents |
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 dbms_utility.format_error_backtrace example in oracle Overflow the company Business Learn more about hiring developers or posting ads with us Stack
How To Find Which Line Error Was Raised In Oracle
Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community pl sql call stack of 6.2 million 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
Oracle Call Stack Trace
vote favorite 4 Sometimes 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 format_error_stack vs format_error_backtrace edited Aug 12 '11 at 2:50 APC 87.3k1384184 asked Aug 11 '11 at 20:26 Revious 1,749135495 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,79432544 answered Aug 11 '11 at 20:36 Justin Cave 160k14204250 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,79432544 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,79432544 answered Oct 27 '14 at 13:30 xmedeko 1,99411836 add a comment| up vote 0 down vote I use
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Utl_call_stack
Home » Articles » 12c » Here UTL_CALL_STACK : Get Detailed Information About
What Are The Methods There In Save Exceptions In Oracle
the Currently Running Subprogram in Oracle Database 12c Release 1 (12.1) Oracle database 12c introduced the UTL_CALL_STACK package to dbms_utility.format_error_backtrace 11g allow programmatic access to the call 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 http://stackoverflow.com/questions/7032373/oracle-pl-sql-how-to-get-the-stack-trace-package-name-and-procedure-name 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 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, https://oracle-base.com/articles/12c/utl-call-stack-12cr1 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_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 cal
log in tour help Tour Start 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 http://dba.stackexchange.com/questions/96573/stack-trace-of-oracle-sql-execution about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer http://www.oracle-developer.net/display.php?id=318 site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a in oracle question Anybody can answer The best answers are voted up and rise to the top Stack trace of oracle sql execution up vote 0 down vote favorite Is there a way to get the stack trace of the SQL query that is given in awr, I mean a way to check the flow of the query from which package or procedure that it has been triggered? tkprof and awr both show oracle get error only the SQL and the elapsed time, parse and fetches. oracle oracle-11g-r2 share|improve this question edited Mar 30 '15 at 8:42 Colin 't Hart 5,02082131 asked Mar 30 '15 at 8:34 anudeepks 1161 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote You mentioned AWR, so you are licensed to use ASH also. You can find the session(s) executing the specific SQL in DBA_HIST_ACTIVE_SESS_HISTORY view based on the sql_id. The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID columns contain information about the PL/SQL call stack. This however may not be complete if you have several levels of PL/SQL packages, procedures or functions built on top of each other. If you know the sql_id and that the SQL statement will be executed in the future, you can set a trace event to dump an errorstack for this specific statement (which can cause quite big overhead and create huge trace files, but I could not come up with a more effective method). Given the below example: create or replace package mypackage as procedure myprocedure; end; / create or replace package body mypackage as procedure myprocedure as x number; begin select count(*) into x from user_objects; end; end; / execute mypackage.myprocedure; select distinct sql_id, sql_text from v$sql where lower(sql_text) like '%us
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 / O