Re: help about ora_sql_txt(sql_text)

Interesting.

It worked for me.

On 8/1/06, xiaoyan <xiaoyezi.xyz@xxxxxxx> wrote:

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

Other related posts: