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

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 12 Jun 2007 10:46:42 -0400

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kerber, Andrew W.
        Sent: Tuesday, June 12, 2007 9:06 AM
        To: Krishnadas.Chathamath@xxxxxxxxxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
        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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Krishnadas.Chathamath@xxxxxxxxxxxxxxxxxx
        Sent: Tuesday, June 12, 2007 7:43 AM
        To: oracle-l@xxxxxxxxxxxxx
        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.
        
        
========================================================================
======
        

Other related posts: