Home > Pl Sql > Pl Sql Error Line Number

Pl Sql Error Line Number

Contents

We could easily reverse it to display first to last. -- Procedure to display the call stack. Check DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25plsql-093886.html share|improve this answer edited Nov 7 '13 at 13:32 Saturnix 3,95372863 answered Oct 19 '09 at 15:11 Henry Gao 2,87111315 add a comment| up vote 1 down vote The answers OWNER : The owner of the subprogram associated with the current call. http://setiweb.org/pl-sql/pl-sql-get-line-number-error.php

You can run them and notice the output difference CREATE OR REPLACE PROCEDURE proc_plsql_line IS BEGIN RAISE VALUE_ERROR; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.put_line ( 'Error raised in: '|| $$plsql_unit ||' at Having upgraded to Oracle Database 10g, I can now revisit my proc3 procedure and replace the call to FORMAT_ERROR_STACK with FORMAT_ERROR_BACKTRACE , as shown in Listing 2. So if you are interested in extracting the line number itself, for whatever logging purpose you want, you will need parse the string. Code Listing 4: Revised proc3 calling bt.info CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN DECLARE l_trace bt.error_rt; BEGIN l_trace := bt.info (DBMS_UTILITY.format_error_backtrace); raise_application_error http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html

Pl/sql Line Number

This tool uses JavaScript and much of it will not work correctly without it enabled. 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. put_line ( '*****error found in ' || REGEXP_SUBSTR (SQLERRM, 'line [0-9]*')); DBMS_OUTPUT.put_line ('HERE COMES THE ERROR MESSAGE'); DBMS_OUTPUT.put_line (SQLERRM); END; What do you think? If our procedure is small it is very easy to check the statement that is causing error.But if it is quite large then it is not possible to check every line

SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Error Stack Start ***** ORA-00001: unique constraint (.) violated ORA-06512: at "TEST.TEST_PKG", line 16 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: to know the precise point at which a block of code failed. Code Message --------- --------- -------------------- 5 ORA-01403 no data found 4 ORA-06512 at "TEST.TEST_PKG", line 24 3 ORA-01422 exact fetch returns more than requested number of rows 2 ORA-06512 at "TEST.TEST_PKG", What Are The Methods There In Save Exceptions In Oracle How to pass files found by find as arguments?

Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325194 is a reply to message #325173] Thu, 05 June Oracle Error Stack Trace Show 3 replies 1. SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 6 RAISE; 7 END; 8 / ORA-06512: at line 2 BEGIN * ERROR at line http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown 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 =

On the other hand, we got this information by letting the exception go unhandled. Pl Sql Call Stack l_depth LOOP DBMS_OUTPUT.put_line( RPAD(i, 10) || RPAD(UTL_CALL_STACK.lexical_depth(i), 10) || RPAD(TO_CHAR(UTL_CALL_STACK.unit_line(i),'99'), 10) || RPAD(NVL(UTL_CALL_STACK.owner(i),' '), 10) || RPAD(NVL(UTL_CALL_STACK.current_edition(i),' '), 10) || UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(i)) ); END LOOP; DBMS_OUTPUT.put_line('***** Call Stack End *****'); END; / Select Only Printed Out Cells How to improve this plot? The following example recreates the DISPLAY_BACKTRACE procedure to use the UTL_CALL_STACK package, then re-runs the test. -- Procedure to display the call stack.

Oracle Error Stack Trace

SQL> BEGIN 2 EXECUTE IMMEDIATE 'garbage'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( SQLERRM ); 6 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 7 RAISE; 8 END; 9 / ORA-00900: invalid SQL statement ORA-06512: LEARNINGS Tuesday, May 3, 2011 Error line number in PL/SQL procedure. Pl/sql Line Number 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 How To Find Which Line Error Was Raised? 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

Line Unit --------- --------- -------------------- 3 18 TEST.TEST_PKG 2 13 TEST.TEST_PKG 1 5 TEST.TEST_PKG ***** Backtrace End ***** PL/SQL procedure successfully completed. get redirected here l_depth LOOP DBMS_OUTPUT.put_line( RPAD(i, 10) || RPAD(TO_CHAR(UTL_CALL_STACK.backtrace_line(i),'99'), 10) || UTL_CALL_STACK.backtrace_unit(i) ); END LOOP; DBMS_OUTPUT.put_line('***** Backtrace End *****'); END; / -- Run the test. In some cases, exceptions in nested calls result in different errors being produced by the error handler of the calling routine. Instead of calling and parsing the backtrace function in each exception section, I can call the bt.info function and report on the specifics of the error. $$plsql_line

Steven Feuerstein ([email protected]) is an authority on the PL/SQL language. Mind you, I haven't looked into this seriously since Oracle 8i so it may have changed in more recent versions of the database. This article explores the problem that this function solves and how best to use it. http://setiweb.org/pl-sql/pl-sql-error-handling-line-number.php A major caveat to this is, of course, that if we go back to procedure WILL_ERROR and re-raise the exception in a WHEN OTHERS or such-like, we will once again lose

The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE provides the error stack all the way back to source. Dbms_utility.format_call_stack Example Who Raised That Exception? 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:

The following example recreates the DISPLAY_ERROR_STACK procedure to use the UTL_CALL_STACK package, then re-runs the test. -- Procedure to display the call stack.

The advantage of using this procedure, is that it provides the exact line number in the program using where the error occurs, and not where the procedure call appears However, the This issue has been resolved in 10g or 11g, and 9iR2 is going completely out of support in July. ----------- Sybrand Bakker Senikor Oracle DBA Like Show 0 Likes(0) Actions 2. Senior MemberAccount Moderator Put the step in a package variable, a context, dbms_application_info, each one these can be queried by the caller, you can even but the whole stack but don't Format_error_stack Vs Format_error_backtrace SQL> Starting with the call to DISPLAY_CALL_STACK, we can work back through all the nested calls to the original anonymous block.

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: The only way to identify the actual statement was by removing the WHEN OTHERS so Oracle could tell me the correct line number. http://setiweb.org/pl-sql/pl-sql-error-103-line.php SQL> BEGIN 2 will_error(); 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE( SQLERRM ); 6 DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); 7 END; 8 / ORA-06501: PL/SQL: program error ORA-06512: at "SCOTT.WILL_ERROR", line 3

Depth Number'); DBMS_OUTPUT.put_line('--------- --------- --------- --------- --------- --------------------'); FOR i IN REVERSE 1 .. The implementation of this function is straightforward; the most important thing to keep in mind when writing utilities like this is to keep the code flexible and clearly structured. Just the Line Number, Please In a real-world application, the error backtrace could be very long. 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

SQL> SY. Join them; it only takes a minute: Sign up Is there a way to get the line number where an exception was thrown? However, the good thing about PLSQL_LINE, it provides the number without the need of any extraction, or string parsing. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players. 18 June 2010 Use REGEXP_SUBSTR to get line number on which error was raised?(408)

Send us your comments Popular Downloads Untitled Document Berkeley DB Enterprise Manager Database EE and XE Developer VMs Enterprise Pack for Eclipse Java JDeveloper and ADF Oracle Linux and Oracle VM Or perhaps their front-end applications display the error stack as seen above. SUBPROGRAM : Subprogram name associated with the current call. Senior MemberAccount Moderator Quote:But my question is How to get the Error line number that is causing the exception to throw.

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; What kind of bugs do "goto" statements lead to? How to heal religious units? "Surprising" examples of Markov chains What is the possible impact of dirtyc0w a.k.a. "dirty cow" bug? Avoid exception handlers in intermediate programs in your stack, and call the backtrace function in the exception section of the outermost program in your stack.

SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Call Stack Start ***** Depth Lexical Line Owner Edition Name . Regards, Rajat Ratewal Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325220 is a reply to message #325195]