Pl Sql Error Handling Line Number
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 powerful and flexible exception architecture. Of course, there is pl/sql line number always room for improvement, and in Oracle Database 10g, exception handling takes
How To Find Which Line Error Was Raised?
a big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function what are the methods there in save exceptions in oracle solves and how best to use it. Who Raised That Exception? When an exception is raised, one of the most important pieces of information a programmer would like to uncover is the oracle error stack trace 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. 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
$$plsql_line
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. On the one hand, we should be very pleased with this behavior. Now that we have the line number, we can zoom right in on the problem code and fix it. On the other hand, we got this information by letting the exception go unhandled. In many applications, however, we work to avoid unhan
is a PLS_INTEGER literal value indicating the line number reference to $$PLSQL_LINE in the current program unit. In other words, $$PLSQL_LINE is the number of the line where $$PLSQL_LINE appears in your pl sql call stack PL/SQL code. Another useful predefined inquiry directive is PLSQL_UNIT which is a VARCHAR2 literal value show line number in pl sql developer indicating the current source program unit. For a named compilation unit, $$PLSQL_UNIT contains the unit name. For an anonymous block, $$PLSQL_UNIT is
Dbms_utility.format_call_stack Example
NULL. Now, on to some examples: From an anonymous block: EDDIE@XE> BEGIN 2 DBMS_OUTPUT.put_line ('Line number: ' 3 || $$plsql_line); 4 DBMS_OUTPUT.put_line ('Unit: ' 5 || COALESCE ($$plsql_unit, 'anonymous block') 6 ); 7 END; 8 / Line http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html number: 3 Unit: anonymous block From a procedure: EDDIE@XE> CREATE OR REPLACE PROCEDURE my_proc 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('Line number: ' 5 || $$plsql_line); 6 DBMS_OUTPUT.put_line ('Unit: ' 7 || $$plsql_unit 8 ); 9 END; 10 / Procedure created. EDDIE@XE> exec my_proc; Line number: 5 Unit: MY_PROC From a package: EDDIE@XE> CREATE OR REPLACE PACKAGE my_pkg 2 IS 3 PROCEDURE my_proc; 4 END; 5 / Package created. EDDIE@XE> CREATE OR REPLACE PACKAGE http://awads.net/wp/2006/08/03/heres-a-quick-way-to-get-the-line-number-in-plsql/ BODY my_pkg 2 IS 3 PROCEDURE my_proc 4 IS 5 BEGIN 6 DBMS_OUTPUT.put_line ('Line number: ' || $$plsql_line); 7 DBMS_OUTPUT.put_line ( 'Unit: ' 8 || $$plsql_unit 9 ); 10 END; 11 END; 12 / Package body created. EDDIE@XE> exec my_pkg.my_proc; Line number: 6 Unit: MY_PKG Note that $$PLSQL_UNIT returned the package name, not the procedure name. A few days ago, I blogged about how to find where an error was raised in PL/SQL using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which returns a formatted string that displays a stack of programs and line numbers. But, you must parse the returned string to find the line number and program unit name if you want to use them elsewhere in your programs (like storing them in a log table or using them to query the user_source data dictionary table for example). $$PLSQL_LINE and $$PLSQL_UNIT provide this information without string parsing and manipulation. For example: Using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE: EDDIE@XE> CREATE OR REPLACE PROCEDURE my_proc 2 IS 3 BEGIN 4 RAISE VALUE_ERROR; 5 EXCEPTION 6 WHEN VALUE_ERROR 7 THEN 8 DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace); 9 END; 10 / Procedure created. EDDIE@XE> 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 bit of parsing. That is not the case using $$PLSQL_LINE and $$P
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 http://www.orafaq.com/forum/t/119924/2/ 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 Error Line Number in pl/sql exception block. http://www.oracle-developer.net/display.php?id=318 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 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 to get Error Line Number in PL/SQL in Exception Block [message #325182 is pl sql error 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: 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 thro
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