Pl/sql Exception Error Line Number
Contents |
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 how to get error line number in oracle page. Please enter a title. You can not post a blank pl/sql line number message. Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application oracle error stack trace DevelopmentPL/SQL and SQL This discussion is archived 3 Replies Latest reply on Feb 27, 2010 2:30 PM by Solomon Yakobson Get line number error muttleychess Feb 27, 2010 2:08
How To Find Which Line Error Was Raised
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 advance I have the same question Show 0 Likes(0) 15182Views 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 what are the methods there in save exceptions in oracle 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 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 / BANNE
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 $$plsql_line Us Learn more about Stack Overflow the company Business Learn more about hiring
Pl Sql Call Stack
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the
Show Line Number In Pl Sql Developer
Stack Overflow 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 https://community.oracle.com/thread/1037981 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 message with this piece of code EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Exception message is '||SQLERRM(sqlcode)); ROLLBACK; I'd http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown 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,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 Inq
is very important to find the line number on which the error had occurred. The question is how to find http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ that line number. Before Oracle Database 10g Release 1, the only way to know the line number is to let the exception go unhandled in your PL/SQL code. In Oracle Database http://plsql-challenge.blogspot.com/2010/06/use-regexpsubstr-to-get-line-number-on.html 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 a stack of programs and line numbers line number 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; 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; error line number 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 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.f
Steven Feuerstein, as well as comments from players. 18 June 2010 Use REGEXP_SUBSTR to get line number on which error was raised?(408) In the 17 June 2010 quiz, we asked: "What information about a PL/SQL exception can be obtained inside an exception section of a PL/SQL block by calling a built-in or packaged function provided by Oracle?" One of the choices was "The line number on which the exception was raised". This choice was correct, because you can get this information with a call to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. One of our players, however, offered the following as an alternative: Hi, you'll most probably regard option 1 of today's quiz "the line number on which the exception was raised" as wrong [no, we didn't!]. What I thought when clicking this option as "correct", is described by following example: DECLARE i INTEGER; BEGIN EXECUTE IMMEDIATE ('BEGIN :i := i_dont_exist; END;') USING OUT i; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT. put_line ( '*****error found in ' || REGEXP_SUBSTR (SQLERRM, 'line [0-9]*')); DBMS_OUTPUT.put_line ('HERE COMES THE ERROR MESSAGE'); DBMS_OUTPUT.put_line (SQLERRM); END; What do you think? Is this a good alternative to DBMS_UTILITY.FORMAT_ERROR_BACKTRACE? Posted by Steven Feuerstein at 17:53 2 comments: soumyakanta19 June, 2010 11:27Hi Steven,I feel, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is more reliable in tacing the EXACT line numberwhere error has occured.In the above example, the REGEXP_SUBSTR displays "error found in line 1", where as the actual error is at line number four.Since identifier I_DONT_EXIST is not declared.If we use the following in the EXCEPTION section,the exact line number can be get.DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);Thanks,SoumyakantReplyDeleteSteven Feuerstein23 June, 2010 23:55The bottom line is that the REGEXP_SUBSTR technique will ONLY work with dynamically-executed statements, so it is not a general solution.ReplyDeleteAdd commentLoad more... Newer Post Older Post Home Subscribe to: Post Comments (Atom) Visit PLSQLChallenge.com Follow Us on Twitter Blog Archive ► 2015 (12) ► Jun 14 - Jun 21 (1) ► Jun 7 - Jun 14 (1) ► May 10 - May 17 (1) ► Mar 22 - Mar 29 (1) ► Mar 15 - Mar 22 (1) ► Mar 1 - Mar 8 (1) ► Feb 22 - Mar 1 (3) ► Feb 15 - Feb 22 (1) ► Jan 18 - Jan 25 (1) ► Jan 4 - Jan 11 (1) ► 2014 (13) ► Dec 7 - Dec 14 (1) ► Apr 27 - May 4 (1) ►