Oracle Error 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 dbms_utility.format_error_backtrace example in oracle always room for improvement, and in Oracle Database 10g, exception handling takes a
How To Find Which Line Error Was Raised In Oracle
big step forward with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves
Oracle Error Stack Trace
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
What Are The Methods There In Save Exceptions In Oracle
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 pl/sql line number 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 unhandled exception
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us dbms_utility.format_call_stack example Learn more about Stack Overflow the company Business Learn more about hiring developers or $$plsql_line posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow oracle call stack trace Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Is there a way to get the line number http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html 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 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 http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown (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,36612540 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,95372863 answered Oct 19 '09 at 15:11 Henry Gao 2,87111315 add a comment| up vote 1 down vote The answers have mentioned both, $$PLSQL_LINE & DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. But I would like to add a bit about the difference between them: Predefined Inquiry Directives $$PLSQL_LINE & $$PLSQL_UNIT PLSQL_LINE predefined inq
is a PLS_INTEGER literal value indicating the line number reference to $$PLSQL_LINE in the current http://awads.net/wp/2006/08/03/heres-a-quick-way-to-get-the-line-number-in-plsql/ program unit. In other words, $$PLSQL_LINE is the number of the line where $$PLSQL_LINE appears in your PL/SQL code. Another useful predefined inquiry directive is PLSQL_UNIT which is http://kaamka.blogspot.com/2011/05/error-line-number-in-plsql-procedure.html a VARCHAR2 literal value indicating the current source program unit. For a named compilation unit, $$PLSQL_UNIT contains the unit name. For an anonymous block, $$PLSQL_UNIT is NULL. Now, in oracle 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 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: oracle error line ' 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 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
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.