RE: How to find out the last executed sql statement....

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 12 Jun 2007 10:46:42 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9F50029@usahm208.amer.corp.eds.com>

We use a package with a procedure that logs data to an error table via an anonymous transaction. This commits the tracking/error information even if the end user transaction rolls back. It also logs the data as the process is running instead of dumping it at the end like dbms_output with very little memory requirement.

We place a label on each SQL statement then use exception blocks to record necessary debugging information for error conditions.

During testing extra calls to the log routine can be added to just record where we are at and variable values at specific points in the code. Normally though in production code we only call this routine in exception blocks and pass the statement number for the SQL statement where an error occurred along with key variable names and values (generally the columns in the where clause so the statement action and hopefully error can be duplicated)

Mark D Powell -- Phone (313) 592-5148

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kerber, Andrew W.

	Sent: Tuesday, June 12, 2007 9:06 AM
	To: Krishnadas.Chathamath_at_iflexsolutions.com;
oracle-l_at_freelists.org
	Subject: RE: How to find out the last executed sql statement....
	
	

	The only way I know if is to output each statement using some

form of an output command. Utl file is kind of cumbersome, you could do dbms_output.put_line.

	-----Original Message-----
	From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
Krishnadas.Chathamath_at_iflexsolutions.com

	Sent: Tuesday, June 12, 2007 7:43 AM
	To: oracle-l_at_freelists.org
	Subject: How to find out the last executed sql statement....

	 

	Hi,

	 

	I am a new member of oracle-l and I am finding it very helpful. 

	 

	We are trying to make our Application debug easier. 

	Currently we write debug messages to a file using utl_file

package during the application's execution.

So we can find out till the position where the application runs successfully and where it fails and why it fails (error messages) by looking into the debug file.

Now we are thinking to modify the application debug as follows,

Once the functionality fails we will insert the Session id, Package name and last executed sql (Failed SQL statement or Last successful statement) in a table and from there the developers can get the details. So we need not looking into the debug file at the initial stage.

But how to get the last executed sql statement?

Please help us with your valuable suggestions.

Thanks & Regards

Krishnadas C.K

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 12 2007 - 09:46:42 CDT