Re: PREVIOUS SQL statement

David,

  I have tried to get the previous SQL on a number of versions and I have always found the address and hash value pretty buggy - usually the same as the current SQL. Moreover, SQL statements which have failed are usually pretty hard to catch (no mention in V$SQLAREA, for what I know). I have not used everything which follows in the context of an error-trapping handler, but I have obtained interesting results in another context. Oracle9 has a new function similar to ora_sysevent and the like which returns the statement which has fired a trigger;the documentation seems to imply that it's only available with DDL/Event triggers but in fact it works everywhere (Joe, take note). This is unfortunately not available with 8.1.7 but by toying around you can write yours. Look around V$SQL_CURSOR with the FLAG = 76 condition in your trigger, then join in on V$SQL using the PARENT_HANDLE column
(which should match ADDRESS). Unfortunately you must make it an external
join because CREATE and DROP statements (at least - I have not made an exhaustive check yet) do not appear at all as such in V$SQL (however, TRUNCATE and ALTER statements do). However, you can circumvate this with the functions which return operation/object type/object owner/object name - if not the actual statement, it should help you find what has gone wrong (once again, if it works with an AFTER SERVERERROR trigger - I have done it with an ON DDL trigger). Also, beware that some DDL statements such as ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY ... degenerate into TWO statements (alter + create unique index).

Hoping it doesn't sound like Greek to you ...

Stephane Faroult
Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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