Pl Sql Get Line Number Error
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 pl/sql line number powerful and flexible exception architecture. Of course, there is always room how to find which line error was raised? for improvement, and in Oracle Database 10g, exception handling takes a big step forward with the introduction
Oracle Error Stack Trace
of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it. Who Raised That Exception? When an exception is
$$plsql_line
raised, one of the most important pieces of information a programmer would like to uncover is the 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 pl sql call stack 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 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 on
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
Show Line Number In Pl Sql Developer
code. Another useful predefined inquiry directive is PLSQL_UNIT which is a VARCHAR2 literal value indicating dbms_utility.format_call_stack example the current source program unit. For a named compilation unit, $$PLSQL_UNIT contains the unit name. For an anonymous block, $$PLSQL_UNIT is NULL. what are the methods there in save exceptions in oracle 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 number: 3 http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html 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 BODY my_pkg 2 http://awads.net/wp/2006/08/03/heres-a-quick-way-to-get-the-line-number-in-plsql/ 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 $$PLSQL_UNIT: EDDIE@XE> CREATE OR REPLACE PROCED
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 http://www.orafaq.com/forum/t/119924 displays only the error details but not the exact line number where error has occured. http://kaamka.blogspot.com/2011/05/error-line-number-in-plsql-procedure.html 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 pl sql 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 a reply to pl sql get 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 throw. Just remove "when others" Rega
than it can show what exception was raised.We will never know in which line exception was thrown.Best approach will be to use combination of SQLERRM and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()Example : If we are calling many functions in single PL/SQL block then we it will give us the exact trace of exception with line number.ORA-06512: at "ABC.XYZ_PROC", line 7056 example --> DECLARE vr_sqlerrm VARCHAR2(1000):=null; BEGIN ----some code here EXCEPTION WHEN OTHERS THEN vr_sqlerrm := SUBSTR( SQLERRM||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),0,999); raise_application_error(-20003,EXCEPTION is --> 'vr_sqlerrm); END; Posted by amardeep at 11:47 PM Labels: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, exact line number, tracking exceptions in oracle 10g No comments: Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Followers Total Pageviews About Me amardeep View my complete profile Blog Archive ► 2016 (8) ► September (8) ► 2014 (1) ► July (1) ► 2013 (1) ► August (1) ► 2012 (1) ► May (1) ▼ 2011 (10) ▼ May (10) calculate size of folder in linux Regular Expression for no special character Regular expression in PL/SQL TATA PHOTON + on Ubuntu 10.04 (Lucid Lynx) enable profiler in JDeveloper run jvm in 64 bit mode Java GC (garbage collection) logs analysis Java Thread dump analysis Print bar code in xslt Error line number in PL/SQL procedure. ► 2009 (24) ► September (6) ► July (1) ► June (3) ► May (1) ► April (4) ► March (1) ► February (1) ► January (7) Simple template. Powered by Blogger.