Re: Retrieve SQL Statement in trigger on servererror

From: srivenu <srivenu_at_hotmail.com>
Date: 7 Aug 2002 21:23:46 -0700
Message-ID: <1a68177.0208072023.4b990061@posting.google.com>

You can use this event attribute -
ora_sql_txt (sql_text out
ora_name_list_t)
See the Oracle Doc for more info

Its in ORACLE APPLICATION DEVELOPERS GUIDE - FUNDAMENTALS (9.0.1) Chapter 16 - Working with System Events  

The doc says that the servererror event is not fired if the error is caused by the following errors.

ORA-01403: data not found

ORA-01422: exact fetch returns more than requested number of rows

ORA-01423: error encountered while checking for extra rows in exact fetch

ORA-01034: ORACLE not available

ORA-04030: out of process memory

Well if you are interested in only the Last SQL statement, you can use this

col sid form 999

col curr form a40 head "     Current SQL"
col prev form a40 head "     Previous SQL"

bre on sid skip 2
select a.sid sid,max(b.sql_text) curr,max(c.sql_text) prev from v$session a, v$sql b,v$sql c
where a.sql_address=b.address
and a.prev_sql_addr=c.address
group by sid
/
cle bre Received on Wed Aug 07 2002 - 23:23:46 CDT