Capture SQL stmt from ORA-54

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Capture SQL stmt from ORA-54

Capture SQL stmt from ORA-54

2005-07-02 - By Herring Dave - dherri

Reply: 1 2 3

Is there any way to capture the SQL statement that generates an ORA-54 (See ORA-54.ora-code.com)?
I've got a trigger set on my database to capture all possible ORA-
errors, but for ORA-54 (See ORA-54.ora-code.com) 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@(protected) <mailto:dherri@(protected)> >

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

**********************************************************************
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.

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas
-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>

</style>

</head>

<body lang=EN-US link=blue vlink="#606420">

<div class=Section1>

Is there any way to capture the SQL statement that generates
an ORA-54 (See ORA-54.ora-code.com)?  I've got a trigger set on my database to capture all possible
ORA-
errors, but for ORA-54 (See ORA-54.ora-code.com) 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.<o:p></o:p>

<o:p> </o:p>

Here's the text of my trigger:<o:p></o:p>

<o:p> </o:p>

CREATE OR REPLACE TRIGGER SYS.AUD_ORA_ERROR_SETG<o:p></o:p>


        AFTER
SERVERERROR ON DATABASE<o:p></o:p>

         &nbsp
;  WHEN (DBMS_STANDARD.SERVER_ERROR(1) NOT IN
(1003,1017,1594,1595,1720)) DECLARE<o:p></o:p>

   v_machine      
      VARCHAR2(64);<o:p></o:p>

   v_osuser      
       VARCHAR2(30);<o:p></o:p>

   v_process      
      VARCHAR2(12);<o:p></o:p>

   v_program      
      VARCHAR2(48);<o:p></o:p>

   v_sql_stmt      
     VARCHAR2(4000);<o:p></o:p>

   v_sql_text      
     ora_name_list_t;<o:p></o:p>

   v_tmp_counter     
   PLS_INTEGER;<o:p></o:p>

   v_tmp_sql_chunks     PLS
_INTEGER;<o:p></o:p>

BEGIN<o:p></o:p>

   BEGIN<o:p></o:p>

      SELECT s.osuser<o:p></o:p>

         &nbsp
; , s.machine<o:p></o:p>

         &nbsp
; , p.spid<o:p></o:p>

         &nbsp
; , s.program<o:p></o:p>

        INTO v_osuser, v
_machine, v_process, v_program<o:p></o:p>

        FROM sys.v_
$session s<o:p></o:p>

         &nbsp
; , sys.v_$process p<o:p></o:p>

       WHERE s.sid = (SELECT
/*+ FIRST_ROWS(1) */ sid<o:p></o:p>

         &nbsp
;             
FROM sys.v_$mystat<o:p></o:p>

         &nbsp
;             WHERE
rownum < 2)<o:p></o:p>

         AND s
.username = ora_login_user<o:p></o:p>

         AND s
.paddr = p.addr;<o:p></o:p>

   EXCEPTION<o:p></o:p>

      WHEN no_data_found THEN<o:p>
</o:p>

         v_sql_stmt
:= '';<o:p></o:p>

   END;<o:p></o:p>

   IF DBMS_STANDARD.SERVER_ERROR(1) IN (900,1400
,1401,1722)<o:p></o:p>

      OR DBMS_STANDARD.SERVER
_ERROR(1) BETWEEN 900 AND 985
THEN<o:p></o:p>

      v_tmp_sql_chunks := ora_sql
_txt(v_sql_text);<o:p></o:p>

      FOR v_tmp_counter IN 1..v
_tmp_sql_chunks LOOP<o:p></o:p>

/*<o:p></o:p>

 * Chunks are set at 64K each and since the max VACHAR2
variable is 4000, going beyond<o:p></o:p>

 * 63 loops will cause an overflow.<o:p></o:p>

 */<o:p></o:p>

         IF v_tmp
_counter < 63 THEN<o:p></o:p>

         &nbsp
;  v_sql_stmt := v_sql_stmt ||
v_sql_text(v_tmp_counter);<o:p></o:p>

         ELSE<o:p><
/o:p>

          
  EXIT;<o:p></o:p>

         END IF;<o
:p></o:p>

      END LOOP;<o:p></o:p>

   END IF;<o:p></o:p>

   INSERT INTO gmaudit.aud_ora_error_tb VALUES<o
:p></o:p>

    (DBMS_STANDARD.SERVER_ERROR(1),sysdate
,ora_login_user,<o:p></o:p>

    
gmaudit.ora_error_nbr_seq.nextval,v_osuser,v_machine,v_process,v_program,v_sql
_stmt);<o:p></o:p>

END;<o:p></o:p>

/<o:p></o:p>

<o:p> </o:p>

Dave<o:p></o:p>

-- ---- ---- ---- ---- ---- ---- ----<o:p></o:p>

Dave Herring, DBA<o:p></o:p>


Acxiom Corporation<o:p></o:p
>

3333 Finley<o:p></o:p>

Downers Grove, IL 60515<o:p>
</o:p>

wk: 630.944.4762<o:p></o:p>

<<a href="mailto:dherri@(protected)">mailto:dherri@(protected)</a>><o:p></o:p>

-- ---- ---- ---- ---- ---- ---- ----<o:p></o:p>

<o:p> </o:p>

</div>

 
 
********************************************************************** 
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. 

</body>

</html>