Home » SQL & PL/SQL » SQL & PL/SQL » Exception handler
Exception handler [message #250328] Mon, 09 July 2007 12:15 Go to next message
Majaa
Messages: 5
Registered: May 2007
Location: Tamilnadu
Junior Member
Whenever exceptions throws ,i want to get more information for debugging purpose in ORACLE 9i.

Shall we get Last executed DML in that procedure ?

How can we find that the exception throws from which line number in the Procedure or package ?

[Updated on: Mon, 09 July 2007 12:19] by Moderator

Report message to a moderator

Re: Exception handler [message #250329 is a reply to message #250328] Mon, 09 July 2007 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Shall we get Last executed DML in that procedure ?

No.
Quote:
How can we find that the exception throws from which line number in the Procedure or package ?

No way in 9i.

You have to know where you come from.
After all, it is your code, you can add a variable that indicates you where you are.
You can have multiple level of PL/SQL blocks each ones with its exception block...

Regards
Michel
Re: Exception handler [message #250388 is a reply to message #250329] Mon, 09 July 2007 21:38 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The following should give you the text of the last SQL run:
select  sql_text
from    v$sqltext t
where   (t.hash_value,t.address) IN (
        select  prev_hash_value
        ,       prev_sql_addr
        from    v$session
        where   audsid = sys_context('USERENV','SESSIONID')
)
order by piece
/

I don't know whether it is rock-solid guaranteed, but it seems to work so long as the SQL parses successfully. If you had a Table or View Does Not Exist error, it would probably give you the previous successfully parsed SQL.

A slightly better SQL for 10g is:
select  sql_fulltext
from    v$sql t
where   (t.hash_value, t.address, t.child_number, t.sql_id) IN (
        select  prev_hash_value
        ,       prev_sql_addr
        ,       prev_child_number
        ,       prev_sql_id
        from    v$session
        where   audsid = sys_context('USERENV','SESSIONID')
)
/


Ross Leishman
Re: Exception handler [message #250413 is a reply to message #250328] Tue, 10 July 2007 00:03 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Majaa wrote on Mon, 09 July 2007 19:15
How can we find that the exception throws from which line number in the Procedure or package ?


By removing all of your exception handlers
Previous Topic: Data not found
Next Topic: mixing rows
Goto Forum:
  


Current Time: Thu Dec 08 08:36:42 CST 2016

Total time taken to generate the page: 0.27767 seconds