Interesting.
It worked for me.
Hi, Thank you very much,but the trigger you offered still does not work,i can not capture the dml statment because I always get the exception,I think may this is because the value of ora_sql_txt(sql_text) is null which raise the exception? Any other suggestion?My envirment is Windows XP+ Oracle10.2
----- Original Message ----- *From:* Jared Still <jkstill@xxxxxxxxx> *To:* xiaoyezi.xyz@xxxxxxx *Cc:* oracle-l@xxxxxxxxxxxxx *Sent:* Tuesday, August 01, 2006 11:51 PM *Subject:* Re: help about ora_sql_txt(sql_text)
I'm not sure what you're attempting to do with that first loop, but both it and the state_sql variable are unneeded.
Perhaps a perusal of the docs is in order?
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg14evt.htm#1000872
This trigger works:
CREATE OR REPLACE TRIGGER test AFTER insert ON t declare sql_text ora_name_list_t; BEGIN
FOR i IN 1..ora_sql_txt(sql_text) 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,sql_text(i)); END LOOP; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20333,'There is exception!' ); end test;
On 8/1/06, xiaoyan <xiaoyezi.xyz@xxxxxxx> wrote: > > 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? >
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist