SQL & PL/SQL

Hello,
I am trying to get the SQL Test that is ether inserting or updating a row in one of my tables. We are finding rows with an DATETIMEIN column value greater then the DATERECORDED (sysdate). I could put a check constraint on the table to block such rows but this will cause an error and I am not sure what the application will do. I need to be as benign as possible.

I am trying to create a trigger on the table that will grab the SQL Text and maybe give us a clue how this is happening. I have tried a number of approaches and this code represents the two best I have come up with. However nether are the great. Option #1 never seems to get the correct SQL Text because of other triggers on the table I think. Option #2 gets the SQL but I am not confident it will get the SQL from the session that did the DDL on the table and fired the trigger as this is a real time and have many sessions posting to this table.

Any ideas would be great. I could even be going after this completely wrong and am open to any other ideas.

The Oracle version is 10.2.0.4.

create or replace 
trigger EVENTLOG_FUTURE_DATE_TRAP
after insert or update on EVENTLOG
for each row
declare
  vMsg VARCHAR2(4000);
  vSQL VARCHAR2(4000);
begin

if :NEW.DATETIMEIN > (sysdate + .1) then

/* Option #1 */
    select 
      replace(trim('~' from max(sys_connect_by_path(SQL_TEXT,'~')) keep (dense_rank last order by CURR)),'~','') as FULL_SQL_TEXT -- Works
    into
      vSQL
    from
      (
      select 
          S.USERNAME, S.SID as CURRENT_SID, S.SERIAL#, T.PIECE,
          T.SQL_TEXT,
          ROW_NUMBER() over (partition by S.USERNAME, S.SID, S.SERIAL# order by T.PIECE) as CURR,
          ROW_NUMBER() over (partition by S.USERNAME, S.SID, S.SERIAL# order by T.PIECE) -1 as PREV
      from 
        V$SESSION S,
        V$SQLTEXT_WITH_NEWLINES T
      where 
        S.SQL_ID is not null
        and S.PREV_SQL_ID = T.SQL_ID
        and S.SID = sys_context('USERENV','SID')
      order by 
        T.PIECE
      )
    connect by PREV = prior CURR
    start with CURR = 1; 


/* Option #2 */
/*    if INSERTING then
      select SQL_FULLTEXT into vSQL from (
      select * from V$SQLAREA where upper(SQL_FULLTEXT) like 'INSERT%PTNRATING%' order by LAST_LOAD_TIME desc)
      where rownum = 1; 
    else
      select SQL_FULLTEXT into vSQL from (
      select * from V$SQLAREA where upper(SQL_FULLTEXT) like 'UPDATE%PTNRATING%' order by LAST_LOAD_TIME desc)
      where rownum = 1; 
    end if;    
    vSQL := substr(vSQL,1,1024);
*/    
    select
      'HOST: ' || SYS_CONTEXT('USERENV', 'HOST') || ' | ' ||
      'OS_USER: ' || SYS_CONTEXT('USERENV', 'OS_USER')  || ' | ' ||
      'SESSION_USER: ' || SYS_CONTEXT('USERENV', 'SESSION_USER')  || ' | ' ||
      'IP_ADDRESS: ' || SYS_CONTEXT('USERENV', 'IP_ADDRESS')  || ' | ' ||
      'TERMINAL: ' || SYS_CONTEXT('USERENV', 'TERMINAL')  || ' | ' ||
--  set through the DBMS_APPLICATION_INFO package
      'CLIENT_IDENTIFIER: ' || SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') || ' | ' ||
      'CLIENT_INFO: ' || SYS_CONTEXT('USERENV', 'CLIENT_INFO') || ' | ' ||      
      'MODULE: ' || SYS_CONTEXT('USERENV', 'MODULE') || ' | ' ||
      'ACTION: ' || SYS_CONTEXT('USERENV', 'ACTION') 
    into 
      vMsg
    from 
      dual;
    vMsg := 'EVENTLOGID: ' || :NEW.EVENTLOGID || 
    ' | ' || 'OLD.DATETIMEIN: ' || to_char(:OLD.DATETIMEIN,'MM/DD/YYYY HH:MIPM') || ' | ' || 'NEW.DATETIMEIN: ' || to_char(:NEW.DATETIMEIN,'MM/DD/YYYY HH:MIPM') || 
    ' | ' || 'OLD.DATETIMEOUT: ' || to_char(:OLD.DATETIMEOUT,'MM/DD/YYYY HH:MIPM') || ' | ' || 'NEW.DATETIMEOUT: ' || to_char(:NEW.DATETIMEOUT,'MM/DD/YYYY HH:MIPM') || 
    ' | ' || vMsg; 
    vMsg := substr(vMsg,1,1024);

   insert into EVENTLOG_DEBUG 
      (DEBUGID, 
      PROC, 
      VALUES_IN, 
      VALUES_OUT)
		values 
      (SEQ_DEBUG.NEXTVAL, 
      'eventlog_future_date_trap',
      vMsg,
      vSQL);
  end if;      
end;