Home » SQL & PL/SQL » SQL & PL/SQL » How to get Error Line Number in PL/SQL in Exception Block (Oracle 9i,9.2.0.6.0,Windows XP)
How to get Error Line Number in PL/SQL in Exception Block [message #325159] Thu, 05 June 2008 03:17 Go to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
When we use exception block 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 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??

Re: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 message #325173] Thu, 05 June 2008 04:38 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: 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.



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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But my question is How to get the Error line number that is causing the exception to throw.

Just remove "when others"

Regards
Michel
Re: How to get Error Line Number in PL/SQL in Exception Block [message #325192 is a reply to message #325185] Thu, 05 June 2008 04:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I feel with rajatratewal, though.

I write a lot of PL/SQL that is used by customers frontends, and I like to log my errors before raising them since I don't have control over the frontends handling or even displaying them.

What I do then is add some step-names to the procedure like :

declare
v_step varchar2(10);
begin

v_step = 'INIT';
-- do something

v_step = 'CALCULATE';
-- do something

v_step = 'VERIFY';
-- do something

WHEN OTHERS THEN
     log_message( 10, 'Error Step ' || v_step ||' : ' || sqlerrm);
     raise;
end;


where "log_message(errorlevel, message)" is my custom procedure to log away messages into a log table with an error level.
Re: How to get Error Line Number in PL/SQL in Exception Block [message #325194 is a reply to message #325173] Thu, 05 June 2008 04:59 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Michel i wan't to store that line number in a logger table.

My requirement is this.I hope this clarifies.

Begin
 /*
 Some Statements
 1 Statement 1
 2 Statement 2--Has errors
      .
      .
 n Ststement n

*/
When NO_DATA_FOUND then
  Record_error(linenumber,sqlerrm);--linenumber is 2 
End; 





Re: How to get Error Line Number in PL/SQL in Exception Block [message #325195 is a reply to message #325192] Thu, 05 June 2008 05:03 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Thanks ThomasG you got it right.

But don't you think this is tedious work to do??

Is their no other means by which we can achieve this.
Re: How to get Error Line Number in PL/SQL in Exception Block [message #325198 is a reply to message #325195] Thu, 05 June 2008 05:06 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
But don't you think this is tedious work to do??


I don't use it everywhere, just in spots where it would be even more tedious to track down bugs without it. Wink
Re: How to get Error Line Number in PL/SQL in Exception Block [message #325208 is a reply to message #325198] Thu, 05 June 2008 05:16 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
ThomasG so you are trying to say that Oracle 9.2.0.6.0 does not provide this functionality in PL/SQL to record error line number.



Re: How to get Error Line Number in PL/SQL in Exception Block [message #325210 is a reply to message #325192] Thu, 05 June 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the step in a package variable, a context, dbms_application_info, each one these can be queried by the caller, you can even but the whole stack but don't use "when others then" for this.

Regards
Michel
Re: How to get Error Line Number in PL/SQL in Exception Block [message #325217 is a reply to message #325210] Thu, 05 June 2008 05:34 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Thanks all for your valuable suggestions.

Regards,
Rajat Ratewal
Re: How to get Error Line Number in PL/SQL in Exception Block [message #325220 is a reply to message #325195] Thu, 05 June 2008 05:39 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
I use the following statement in the WHEN OTHERS Section

DBMS_OUTPUT.put_line (dbms_utility.format_error_backtrace());


When an exception occurs, i get an output like

ORA-06512: at "MYSCHEMA.MYPACKAGE", line 1764
ORA-06512: at "MYSCHEMA.MYPACKAGE", line 2328


Regards
Minto
Re: How to get Error Line Number in PL/SQL in Exception Block [message #325222 is a reply to message #325220] Thu, 05 June 2008 05:44 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
mintomohan you must be using 10g.

i am using 9i.In 9i we don't have this procedure

regards,
Rajat
Re: How to get Error Line Number in PL/SQL in Exception Block [message #325226 is a reply to message #325220] Thu, 05 June 2008 05:48 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Minto,

At least read the original post before answering.

Regards
Michel
Previous Topic: sql query problem
Next Topic: execute immediate with nvarchar data type
Goto Forum:
  


Current Time: Fri Dec 09 15:56:07 CST 2016

Total time taken to generate the page: 0.36973 seconds