Oracle Pl/sql Get Error Line Number
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
Dbms_utility.format_error_backtrace Example In Oracle
line number is to let the exception go unhandled in your PL/SQL code. In Oracle Database how to find which line error was raised in oracle 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 pl/sql line number 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
What Are The Methods There In Save Exceptions In Oracle
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
Oracle Error Stack Trace
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", 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 thi
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. pl sql call stack Please type your message and try again. More discussions in PL/SQL and SQL All
$$plsql_line
PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 3 Replies Latest reply on Feb 27, 2010 2:30 PM by Solomon dbms_utility.format_call_stack example Yakobson Get line number error muttleychess Feb 27, 2010 2:08 PM Hi Is there some way to get line number error when there is some exception ? I use oracle 9.2.02 thank you in http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ advance I have the same question Show 0 Likes(0) 15169Views Tags: none (add) This content has been marked as final. Show 3 replies 1. Re: Get line number error sybrand_b Feb 27, 2010 2:12 PM (in response to muttleychess) When you don't trap the exception you will get a line number, of the line in error. When you trap the exception you need to use dbms_utility.format_error_stack. Thid will not provide https://community.oracle.com/thread/1037981 correct line numbers. 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. Re: Get line number error Solomon Yakobson Feb 27, 2010 2:17 PM (in response to muttleychess) Not in 9.2, AFAIK. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE was introduced in 10g. SY. Like Show 0 Likes(0) Actions 3. Re: Get line number error Solomon Yakobson Feb 27, 2010 2:30 PM (in response to sybrand_b) sybrand_b wrote: When you trap the exception you need to use dbms_utility.format_error_stack. Thid will not provide correct line numbers. This issue has been resolved in 10g or 11g, and 9iR2 is going completely out of support in July.FORMAT_ERROR_STACK will not provide any line numbers: SQL> select * from v$version 2 / BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production SQL> set serveroutput on format word_wrapped SQL> begin 2 raise no_data_found; 3 exception 4 when no_data_found 5 then 6 dbms_output.put_line('ERROR_STACK: ' || DBMS_UTILITY.FORMAT_ERROR_STACK); 7 dbms_output.put_line('ERROR_BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); 8 end; 9 / ERROR_STACK:
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 http://www.orafaq.com/forum/t/119924/2/ in pl/sql and use SQLERRM it displays only the 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 http://www.oracle-developer.net/display.php?id=318 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: in oracle How to get Error Line Number in PL/SQL in Exception Block [message #325173 is a reply to message #325159] Thu, 05 June 2008 04:26 Michel Cadot Messages: 63912Registered: 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 oracle pl/sql get 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: 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: 63912Registered: Marc
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 that the point in the code where the exception was raised moves to the explicit RAISE call, as can be seen at the end of the error stack above (highlighted in bold). The application logs would now record the fact that an ORA-00900 was raised, but in a scaled-up application, i