FreeLists / oracle-l / Capture SQL stmt from ORA-54

["Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>]

Is there any way to capture the SQL statement that generates an ORA-54?
I've got a trigger set on my database to capture all possible ORA-
errors, but for ORA-54 errors, only the USERNAME, OSUSER, and PROGRAM
get captured.  I'm running on Oracle 9.2.0.4 on Tru64 5.1.  Any help
would be appreciated.

 

Here's the text of my trigger:

 

CREATE OR REPLACE TRIGGER SYS.AUD_ORA_ERROR_SETG

        AFTER SERVERERROR ON DATABASE

            WHEN (DBMS_STANDARD.SERVER_ERROR(1) NOT IN
(1003,1017,1594,1595,1720)) DECLARE

   v_machine            VARCHAR2(64);

   v_osuser             VARCHAR2(30);

   v_process            VARCHAR2(12);

   v_program            VARCHAR2(48);

   v_sql_stmt           VARCHAR2(4000);

   v_sql_text           ora_name_list_t;

   v_tmp_counter        PLS_INTEGER;

   v_tmp_sql_chunks     PLS_INTEGER;

BEGIN

   BEGIN

      SELECT s.osuser

           , s.machine

           , p.spid

           , s.program

        INTO v_osuser, v_machine, v_process, v_program

        FROM sys.v_$session s

           , sys.v_$process p

       WHERE s.sid = (SELECT /*+ FIRST_ROWS(1) */ sid

                        FROM sys.v_$mystat

                       WHERE rownum < 2)

         AND s.username = ora_login_user

         AND s.paddr = p.addr;

   EXCEPTION

      WHEN no_data_found THEN

         v_sql_stmt := '';

   END;

   IF DBMS_STANDARD.SERVER_ERROR(1) IN (900,1400,1401,1722)

      OR DBMS_STANDARD.SERVER_ERROR(1) BETWEEN 900 AND 985 THEN

      v_tmp_sql_chunks := ora_sql_txt(v_sql_text);

      FOR v_tmp_counter IN 1..v_tmp_sql_chunks LOOP

/*

 * Chunks are set at 64K each and since the max VACHAR2 variable is
4000, going beyond

 * 63 loops will cause an overflow.

 */

         IF v_tmp_counter < 63 THEN

            v_sql_stmt := v_sql_stmt || v_sql_text(v_tmp_counter);

         ELSE

            EXIT;

         END IF;

      END LOOP;

   END IF;

   INSERT INTO gmaudit.aud_ora_error_tb VALUES

    (DBMS_STANDARD.SERVER_ERROR(1),sysdate,ora_login_user,

 
gmaudit.ora_error_nbr_seq.nextval,v_osuser,v_machine,v_process,v_program
,v_sql_stmt);

END;

/

 

Dave

-------------------------------------

Dave Herring, DBA

Acxiom Corporation

3333 Finley

Downers Grove, IL 60515

wk: 630.944.4762

<mailto:dherri@xxxxxxxxxx <mailto:dherri@xxxxxxxxxx> >

-------------------------------------

 



**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.