How To Find Error Line Number In Pl/sql
Contents |
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 dbms_utility.format_error_backtrace example in oracle let the exception go unhandled in your PL/SQL code. In Oracle Database 10g Release 1 and
Pl/sql Line Number
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
What Are The Methods There In Save Exceptions In Oracle
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;
How To Find Which Line Error Was Raised?
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 oracle error stack trace 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", line 5 ORA-06512: at "HR.P2", line 6 ORA-06512: at "HR.P3", line 5 PL/SQL procedure successfully completed. The procedure p3 successfully completed and returned the execution stack at the point where the exception was raised. The first line of the stack is where the exception was raised. In this example, it was at "HR.P1", line 5. In the above example, the call to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE was from the exce
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and $$plsql_line policies of this site About Us Learn more about Stack Overflow the pl sql call stack company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users dbms_utility.format_call_stack example Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ a minute: Sign up Is there a way to get the line number where an exception was thrown? up vote 9 down vote favorite 3 Im working on a pl-sql script, in which I have about 10 TO_CHAR conversions. One of them is throwing an ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception. Currently, im logging the http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown 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 exception is thrown, in order to receive a message in the form of ORA-06502: PL/SQL: numeric or value error: character string buffer too small (at line x) Is there an easy way to do this? 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 to use DBMS_OUTPUT.put_line('Error in '|| $$plsql_unit || ' at ' || $$plsql_line); also look into using DBMS_UTILITY.format_error_backtrace there is an article in Oracle Magazine from april '05 by Steven Feuerstein: http://www.oracle.com/technetwork/issue-archive/2005/05-mar/o25plsql-093886.html Cheers, niels share|improve this answer edited Nov 7 '13 at 13:32 Malice 2,35612539 answered Oct 19 '09 at 15:25 Niels Castle 6,8622048 add a comment| up vote 2 down vote you need 10g or above. 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,94372863 answered Oct 19 '09 at 15:11 Henry Gao 2,851113
E-mail to friend How to get Error Line Number in PL/SQL in Exception Block [message #325159] Thu, 05 June 2008 03:17 rajatratewal Messages: 507Registered: March 2008 Location: INDIA Senior Member When we use exception block in pl/sql and use SQLERRM it displays only the http://www.orafaq.com/forum/t/119924 error details but not the exact line number where error has occured. But if we don't use exception block line number is also displayed. How to get Error Line Number in pl/sql exception block. Is their any method to achieve this in 9i. 10g we have DBMS_UTILITY Backtrace procedure to achieve this. Any ideas?? Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325173 is a reply to how to message #325159] Thu, 05 June 2008 04:26 Michel Cadot Messages: 63888Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator Of course, the first question should be why do you use sqlerrm? "When others then dbms_output.put_line(sqlerrm)"? 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 a reply to message #325173] Thu, 05 June 2008 04:38 rajatratewal Messages: 507Registered: how to find March 2008 Location: INDIA Senior Member Yes you are right Michel I am using it with others clause. I can replace it with built in or custom exceptions like NO_DATA_FOUND etc. But my question is How to get the Error line number that is causing the exception to throw. If we run a pl/sql procedure without exception block then in Sql* plus we can see the line number where error has occured. 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 in debug mode.because in b/w begin and end clause we can have number of statements and one exception block to handle all the exceptions. Is their any method available in oracle 9i by which i can trace the statement that has caused the exception to throw. Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325185 is a reply to message #325182] Thu, 05 June 2008 04:44 Michel Cadot Messages: 63888Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator Quote:But my question is How to get the Error line number that is causing the exception to throw. Just remove "when others" Regards Michel Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exceptio