help about ora_sql_txt(sql_text)

From: xiaoyan <xiaoyezi.xyz_at_163.com>
Date: Tue, 1 Aug 2006 23:01:36 +0800
Message-ID: <00b501c6b57b$6329da90$d88870ca@buaad538c81ca1>

How to get the sql text when a user executes a dml statment? I wrote a trigger like this:(a triiger on the table 'T' of schema 'TEST')

CREATE OR REPLACE TRIGGER test
  AFTER insert ON test.t
declare

    sql_text ora_name_list_t;
    state_sql dml_trace.ddl_sql%TYPE;
BEGIN

       FOR i IN 1..ora_sql_txt(sql_text) LOOP 
       state_sql := state_sql||sql_text(i); 
       INSERT INTO t VALUES (i);

    END LOOP;    

    INSERT INTO dml_trace(user_name,ipaddress,audit_time,schema_user,schema_object,ddl_sql) 
         VALUES(sys_context('userenv','session_user'),sys_context('userenv','ip_address'),SYSDATE, 
                ora_dict_obj_owner,ora_dict_obj_name,state_sql); 

EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20333,'There is exception!' ); end test;

  but when a user executes a insert on test.t,there is a exception,and through a dbms_output.put_line('ora_sql_txt(sql_text)is'||ora_sql_txt(sql_text)) I found the value of ora_sql_txt(sql_text) is null,then how can I get a dml text?

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 01 2006 - 10:01:36 CDT