How To Use Dbms Utility Format Error Backtrace
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 dbms_utility.format_call_stack example always room for improvement, and in Oracle Database 10g, exception handling takes
How To Find Which Line Error Was Raised In Oracle
a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function
Dbms_utility.format_error_backtrace 11g
solves and how best 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
Format_error_stack Vs Format_error_backtrace
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 calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at which point proc1 raises the NO_DATA_FOUND exception. Notice dbms_utility 12c 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 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
Security Model Constants Types Exceptions Security Model DBMS_UTILITY runs with the privileges of the calling user for the NAME_RESOLVE Procedure and the COMPILE_SCHEMA Procedure. This is necessary so that the SQL works correctly. dbms_utility.format_error_backtrace in oracle forms The package does not run as SYS. The privileges are checked using dbms_utility.format_error_stack vs sqlerrm DBMS_DDL. Constants The DBMS_UTILITY package uses the constants shown in Table 141-1, "DBMS_UTILITY Constants". Table 141-1 DBMS_UTILITY Constants Name oracle call stack trace Type Value Description INV_ERROR_ON_RESTRICTIONS PLS_INTEGER 1 This constant is the only legal value for the p_option_flags parameter of the INVALIDATE subprogram Types dblink_array index_table_type instance_record lname_array name_array number_array uncl_array dblink_array http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html TYPE dblink_array IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER; Lists of database links should be stored here. index_table_type TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER; The order in which objects should be generated is returned here. instance_record TYPE instance_record IS RECORD ( inst_number NUMBER, inst_name VARCHAR2(60)); TYPE instance_table IS TABLE OF instance_record INDEX BY BINARY_INTEGER; The list of active https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_util.htm instance number and instance name. The starting index of instance_table is 1; instance_table is dense. lname_array TYPE lname_array IS TABLE OF VARCHAR2(4000) index by BINARY_INTEGER; Lists of Long NAME should be stored here, it includes fully qualified attribute names. name_array TYPE name_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; Lists of NAME should be stored here. number_array TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; The order in which objects should be generated is returned here for users. uncl_array TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER; Lists of "USER"."NAME"."COLUMN"@LINK should be stored here. Exceptions The following table lists the exceptions raised by DBMS_UTILITY. Table 141-2 Exceptions Raised by DBMS_UTILITY Exception Error Code Description INV_NOT_EXIST_OR_NO_PRIV -24237 Raised by the INVALIDATE subprogram when the object_id argument is NULL or invalid, or when the caller does not have CREATE privileges on the object being invalidated INV_MALFORMED_SETTINGS -24238 Raised by the INVALIDATE subprogram if a compiler setting is specified more than once in the p_plsql_object_settings parameter INV_RESTRICTED_OBJECT -24239 Raised by the INVALIDATE subprogram when different combinations of conditions pertaining to the p_object_id
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 http://stackoverflow.com/questions/7032373/oracle-pl-sql-how-to-get-the-stack-trace-package-name-and-procedure-name the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of http://www.oracle-developer.net/display.php?id=318 4.7 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 vote how to 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 edited Aug 12 how to use '11 at 2:50 APC 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,75432544 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,75432544 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,75432544 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_UTI
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 / ORA-00900: invalid SQL statement BEGIN * ERROR at line 1: ORA-00900: invalid SQL statement ORA-06512: at line 6 The result is th