Home » SQL & PL/SQL » SQL & PL/SQL » how to get the SQL Statement Text that raised an error in Oracle
how to get the SQL Statement Text that raised an error in Oracle [message #191400] Wed, 06 September 2006 03:03 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi All,

I want to store the SQL Statement texts which fails while processing, I tried with :

SELECT sql_text
FROM v$sqltext
WHERE (address, hash_value) IN
(SELECT sql_address,
sql_hash_value
FROM v$session
WHERE audsid = userenv('SESSIONID'))
order by piece;


But if I run that I get the select that I'm currently executing (SELECT
sql_text FROM v$sqltext...).


How can I do to get the previous command and not the current one or even better the exact one that raised an error ?


Thanks & Regards
Amit Pandey
Re: how to get the SQL Statement Text that raised an error in Oracle [message #191422 is a reply to message #191400] Wed, 06 September 2006 05:32 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
and the error which you get is?
Re: how to get the SQL Statement Text that raised an error in Oracle [message #191725 is a reply to message #191400] Thu, 07 September 2006 23:47 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Submitted by using "Report message to a moderator".

I cannot get any solution from oracle documentation and even many of the sites available. And i'm in very urgent need of this. I know this may be a wrong way to report me problem but i'm in trouble, Please help me out Regards Amit

This is NOT the way to get an answer.

Just put a 'reply' to your own post asking for assistance AND SHOWING HOW YOU HAVE EVOLVED YOUR QUERY. Your message will be placed at the beginning of the forum and hopefully looked at again by the membership.

David
Re: how to get the SQL Statement Text that raised an error in Oracle [message #191741 is a reply to message #191400] Fri, 08 September 2006 00:24 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Please Assist me, to solve the problem. I've tried many queries and methods but can't get the desired output.
Re: how to get the SQL Statement Text that raised an error in Oracle [message #191786 is a reply to message #191741] Fri, 08 September 2006 02:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Why don't you use trace? Oracle does not keep a history of the SQL you run. You could also look at auditing.

MHE
Re: how to get the SQL Statement Text that raised an error in Oracle [message #191817 is a reply to message #191786] Fri, 08 September 2006 04:21 Go to previous message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi Maaher,

I don't have knowledge of trace, how to use it. I'm reading the oracle documentation for the same. Can u tell me by using trace how can we find that sql.
Previous Topic: error help
Next Topic: Displaying '&' in query
Goto Forum:
  


Current Time: Fri Dec 02 13:52:51 CST 2016

Total time taken to generate the page: 0.12983 seconds