PREVIOUS SQL statement

From: david hill <david.hill_at_lechateau.ca>
Date: Mon, 27 May 2002 13:18:19 -0800
Message-ID: <F001.0046C503.20020527131819@fatcity.com>

Hi Guys, I was wondering if someone could help me  

I'm trying to create an AFTER SERVERERROR trigger that will log all errors and the SQL that caused the error.  

Here's what I have so far  

CREATE OR REPLACE TRIGGER server_error_trig

  AFTER SERVERERROR ON DATABASE   DECLARE text varchar2(1000);

  BEGIN   select sql_text into text

      from v$session a , v$sql b

      where a.audsid = sys_context('USERENV','SESSIONID')

      and a.prev_sql_addr = b.address;

    INSERT INTO server_error_log VALUES(ora_sysevent, ora_login_user, SYS_CONTEXT('USERENV','OS_USER'), SYS_CONTEXT('USERENV','CLIENT_INFO'), sysdate, ora_server_error(1), dbms_utility.format_error_stack, text);

  END;   my problem seems to be if I select * from v$session I can see my prev_sql_addr.

BUT If I do this

select prev_sql_addr from v$session where AUDSID = sys_context('USERENV','SESSIONID');

it returns 00  

could someone try the sql above and tell me if works for you.

I'm running 8.1.7.3  

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: david hill
  INET: david.hill_at_lechateau.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon May 27 2002 - 16:18:19 CDT