OraFAQ Forum: SQL & PL/SQL » How to get Error Line Number in PL/SQL in Exception Block

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)

Show: Today's Messages :: Polls :: Message Navigator
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: 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??

Report message to a moderator

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

Michel Cadot
Messages: 68641
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

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.

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: 68641
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

Report message to a moderator

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

ThomasG
Messages: 3211
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.

Report message to a moderator

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

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;

Report message to a moderator

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

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.

Report message to a moderator

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

ThomasG
Messages: 3211
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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68641
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

Report message to a moderator

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

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Thanks all for your valuable suggestions.

Regards,
Rajat Ratewal

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Minto,

At least read the original post before answering.

Regards
Michel

Report message to a moderator

Previous Topic:	sql query problem
Next Topic:	execute immediate with nvarchar data type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 05:09:02 CDT 2024