Home > Pl Sql > Pl Sql Error Handling Line Number

Pl Sql Error Handling Line Number

Contents

CREATE OR REPLACE PROCEDURE display_error_stack AS BEGIN DBMS_OUTPUT.put_line('***** Error Stack Start *****'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line('***** Error Stack End *****'); END; / -- Test package to show a nested call. to know the precise point at which a block of code failed. For this reason, I add following block to every public function or procedure: PROCEDURE test(p_param1 IN NUMBER ,p_param2 IN VARCHAR2) IS BEGIN ... Like Show 0 Likes(0) Actions Go to original post Actions Incoming Links Re: Log exact error line Re: exception that says what line causes error About Oracle Technology Network (OTN)My Oracle http://setiweb.org/pl-sql/pl-sql-get-line-number-error.php

SQL> SY. But I would like to add a bit about the difference between them: Predefined Inquiry Directives $$PLSQL_LINE & $$PLSQL_UNIT PLSQL_LINE predefined inquiry directive is a PLS_INTEGER literal value indicating the line What I do then is add some step-names to the procedure like : declare v_step varchar2(10); begin v_step = 'INIT'; -- do something v_step = 'CALCULATE'; -- do something v_step = 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: http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html

Pl/sql Line Number

Like Show 0 Likes(0) Actions 3. Recognizing that I will be needing to parse the contents of a string based on various delimiters, I define a number of constants to hold these delimiter values. Leave a response Cancel Reply → * Required * Required Notify me of followup comments via e-mail. Depth Number --------- --------- --------- --------- --------- -------------------- 1 0 13 TEST DISPLAY_CALL_STACK 2 1 15 TEST TEST_PKG.PROC_3 3 1 10 TEST TEST_PKG.PROC_2 4 1 5 TEST TEST_PKG.PROC_1 5 0 1

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. i am using 9i.In 9i we don't have this procedure regards, Rajat Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception The "ORA-06512" error is not included, but this is implied because it is a backtrace message. $$plsql_line In many applications, however, we work to avoid unhandled exceptions.

In an exception block, the keyword “RAISE” could also be used without an exception name, which can be useful to add logging or to execute clean-up code, before propagating the error. How To Find Which Line Error Was Raised? BEGIN RAISE_APPLICATION_ERROR(-20000,’Logical error occured’); END; If we do not care about the error code and error message, and we will foresee an exception block to directly handle the error, we could 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; EXCEPTION WHEN OTHERS THEN display_backtrace; http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown CREATE OR REPLACE PROCEDURE display_error_stack AS l_depth PLS_INTEGER; BEGIN l_depth := UTL_CALL_STACK.error_depth; DBMS_OUTPUT.put_line('***** Error Stack Start *****'); DBMS_OUTPUT.put_line('Depth Error Error'); DBMS_OUTPUT.put_line('.

Code Listing 2: proc3 rewritten with FORMAT_ERROR_BACKTRACE CREATE 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:'); my_putline (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); END; Pl Sql Call Stack logging plsql share|improve this question asked Oct 19 '09 at 15:10 Tom 23k1495145 add a comment| 4 Answers 4 active oldest votes up vote 8 down vote accepted You need 10g User defined: A logical error which you define and raise yourself System errors could occur from improper coding, like the “ORA-01001: Invalid cursor”, which you should try to fix as soon 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 p1: ORA-06512:

How To Find Which Line Error Was Raised?

Although it is recommended to use proper a description for your errors, instead of recycling error codes. this website [email protected]> exec my_proc; ORA-06512: at "EDDIE.MY_PROC", line 4 If you want to extract "MY_PROC" and "4" from the string ORA-06512: at "EDDIE.MY_PROC", line 4, you will have to do a little Pl/sql Line Number Related 1Oracle - Number to varchar0ORA-06502: PL/SQL: numeric or value error: character string buffer too small error0PL/SQL - prevent ORA-065021Getting PL/SQL: numeric or value error: character to number conversion error0Function substr What Are The Methods There In Save Exceptions In Oracle SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Backtrace Start ***** Depth BTrace BTrace .

In this package, I provide a simple, clean interface as follows: CREATE OR REPLACE PACKAGE bt IS TYPE error_rt IS RECORD ( program_owner all_objects.owner%TYPE , program_name all_objects.object_name%TYPE , line_number PLS_INTEGER ); http://setiweb.org/pl-sql/pl-sql-error-103-line.php This can be a major problem. In this case, it is necessary to parse the backtrace string and retrieve just the top-most entry. Cary Millsap's latest book The Method R Guide to Mastering Oracle Trace Data, Second Edition contains the richest description of Oracle extended SQL trace data that you’ll ever find, and over Oracle Error Stack Trace

Therefore, an important distinction needs to be made between application code that needs to be logged and that which doesn't. On the other hand, we got this information by letting the exception go unhandled. Notice the unhandled VALUE_ERROR exception raised in p1. navigate to this website At last!

The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE provides the error stack all the way back to source. Show Line Number In Pl Sql Developer 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 This allows us to create 1000 unique error codes for our logical errors throughout our application.

But don't you think this is tedious work to do??

The following example recreates the DISPLAY_CALL_STACK procedure to use the UTL_CALL_STACK package, then re-runs the test. -- Procedure to display the call stack. To find the number of X completed, when can I subtract two numbers and when do I have to count? Andy Todd | 25 Jul 2006 9:47 pm I've always found the line numbers provided by the PL/SQL parser to be a little misleading, whenever I've tried to look them up Dbms_utility.format_call_stack Example I write a lot of PL/SQL that is used by customers frontends, and I like to log my errors before raising them since I don't have control over the frontends handling

SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK ); 6 RAISE; 7 END; 8 / ORA-00900: invalid SQL statement BEGIN * ERROR at line SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Call Stack Start ***** Depth Lexical Line Owner Edition Name . When an exception 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. my review here The application logs would now record the fact that an ORA-00900 was raised, but in a scaled-up application, it wouldn't know which statement hit the exception.

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, Thanks. Just like we did for system errors, we can name our user defined errors by using the pragma “EXCEPTION_INIT”. To resolve these bugs, it is important to know where, when and why it happened.

Thus, you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE within an exception section at the top level of your stack and still find out where the error was raised deep within the call stack. Currently, im logging the message with this piece of code EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Exception message is '||SQLERRM(sqlcode)); ROLLBACK; I'd like to add (mostly for debugging purposes) the line where the CREATE OR REPLACE PROCEDURE display_call_stack AS l_depth PLS_INTEGER; BEGIN l_depth := UTL_CALL_STACK.dynamic_depth; DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line('Depth Lexical Line Owner Edition Name'); DBMS_OUTPUT.put_line('. Regards Michel [Updated on: Thu, 05 June 2008 04:30]Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325182 is

I built a utility to do this called the BT package. BACKTRACE_LINE : Line number in the subprogram of the current call. If you feel something is missing, please share your knowledge by leaving a comment. The following simple PL/SQL block demonstrates that Oracle will happily tell us where a procedure, function or anonymous block hit an exception.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope. Database as a Storage (DBaaS) vs. Line Unit'); DBMS_OUTPUT.put_line('--------- --------- --------------------'); FOR i IN REVERSE 1 .. The UTL_CALL_STACK package contains APIs to display the backtrace.