Oracle Raise Error Stack
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 dbms_utility.format_error_backtrace example in oracle powerful and flexible exception architecture. Of course, there is always room
Oracle Pl Sql Error Line Number
for improvement, and in Oracle Database 10g, exception handling takes a big step forward with the how to find which line error was raised in oracle introduction of the 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
Oracle Error Stack Trace
is raised, one of 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. oracle raise exception with message 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 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
March 2012 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 January 2012 March 2012 May 2012 July 2012 September 2012 November 2012 As Published In March/April
Pl Sql Call Stack
2012 TECHNOLOGY: PL/SQL Error Management By Steven Feuerstein Part 6 in
Oracle Call Stack Trace
a series of articles on understanding and using PL/SQL Even if you write absolutely perfect PL/SQL programs, it oracle raise_application_error is possible and even likely that something will go wrong and an error will occur when those programs are run. How your code responds to and deals with that error often http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html spells the difference between a successful application and one that creates all sorts of problems for users as well as developers. This article explores the world of error management in PL/SQL: the different types of exceptions you may encounter; when, why, and how exceptions are raised; how to define your own exceptions; how you can handle exceptions when they occur; and http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html how you can report information about problems back to your users. Exception Overview There are three categories of exceptions in the world of PL/SQL: internally defined, predefined, and user-defined. An internally defined exception is one that is raised internally by an Oracle Database process; this kind of exception always has an error code but does not have a name unless it is assigned one by PL/SQL or your own code. An example of an internally defined exception is ORA-00060 (deadlock detected while waiting for resource). A predefined exception is an internally defined exception that is assigned a name by PL/SQL. Most predefined exceptions are defined in the STANDARD package (a package provided by Oracle Database that defines many common programming elements of the PL/SQL language) and are among the most commonly encountered exceptions. One example is ORA-00001, which is assigned the name DUP_VAL_ON_INDEX in PL/SQL and is raised when a unique index constraint is violated. A user-defined exception is one you have declared in the declaration section of a program unit. User-defined exceptions can be associated with an internally defined exception (that is,
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the http://stackoverflow.com/questions/6020450/oracle-pl-sql-raise-user-defined-exception-with-custom-sqlerrm workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join oracle raise them; it only takes a minute: Sign up Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM up vote 45 down vote favorite 21 Is it possible to create user-defined exceptions and be able to change the SQLERRM? For example: DECLARE ex_custom EXCEPTION; BEGIN RAISE ex_custom; EXCEPTION WHEN ex_custom THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / The output is "User-Defined Exception". Is oracle raise error it possible to change that message? EDIT: Here is some more detail. I hope this one illustrates what I'm trying to do better. DECLARE l_table_status VARCHAR2(8); l_index_status VARCHAR2(8); l_table_name VARCHAR2(30) := 'TEST'; l_index_name VARCHAR2(30) := 'IDX_TEST'; ex_no_metadata EXCEPTION; BEGIN BEGIN SELECT STATUS INTO l_table_status FROM USER_TABLES WHERE TABLE_NAME = l_table_name; EXCEPTION WHEN NO_DATA_FOUND THEN -- raise exception here with message saying -- "Table metadata does not exist." RAISE ex_no_metadata; END; BEGIN SELECT STATUS INTO l_index_status FROM USER_INDEXES WHERE INDEX_NAME = l_index_name; EXCEPTION WHEN NO_DATA_FOUND THEN -- raise exception here with message saying -- "Index metadata does not exist." RAISE ex_no_metadata; END; EXCEPTION WHEN ex_no_metadata THEN DBMS_OUTPUT.PUT_LINE('Exception will be handled by handle_no_metadata_exception(SQLERRM) procedure here.'); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / In reality, there are dozens of those sub-blocks. I'm wondering if there's a way to have a single user-defined exception for each of those sub-blocks to raise, but have it give a different message, instead of creating a separate user-defined exception for each sub-block. In .NET, it would be sort of like having a custom exception like this: publi
is very important to find the line number on which the error had occurred. The question is how to find that line number. Before Oracle Database 10g Release 1, the only way to know the line number is to let the exception go unhandled in your PL/SQL code. In Oracle Database 10g Release 1 and above, you can take advantage of the new function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This new function returns a formatted string that displays a stack of programs and line numbers leading back to the line on which the error was originally raised. For example, prior to 10gR1: SQL> CREATE OR REPLACE PROCEDURE p1 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p1, raising error'); 5 RAISE VALUE_ERROR; 6 END; 7 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE p2 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p2'); 5 DBMS_OUTPUT.put_line ('calling p1'); 6 p1; 7 END; 8 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE p3 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 END; 7 / Procedure created. Notice the unhandled VALUE_ERROR exception raised in p1. Now, Let's call p3: SQL> BEGIN 2 DBMS_OUTPUT.put_line ('calling p3'); 3 p3; 4 END; 5 / BEGIN * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "HR.P1", line 5 ORA-06512: at "HR.P2", line 6 ORA-06512: at "HR.P3", line 5 ORA-06512: at line 3 As expected, by not handling the exception, the procedure returns an error and we are able to know where the exception was raised. In this example, the error ORA-06502: PL/SQL: numeric or value error was raised at "HR.P1", line 5. Starting with 10gR1, you can call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function in your exception handler. Let's use this function in the exception section of procedure p3: SQL> CREATE OR REPLACE PROCEDURE p3 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 EXCEPTION 7 WHEN OTHERS 8 THEN 9 DBMS_OUTPUT.put_line ('Error stack from p3:'); 10 DBMS_OUTPUT.put_line 11 (DBMS_UTILITY.format_error_backtrace); 12 END; 13 / Procedure created. Let's call p3: SQL> set serveroutput on SQL> BEGIN 2 DBMS_OUTPUT.put_line ('calling p3'); 3 p3; 4 END; 5 / calling p3 in p3, calling p2 in p2 calling p1 in p1, raising error Error stack from p3: ORA-06512: at "HR.P1", lin