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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
