Re: help about ora_sql_txt(sql_text)

Interesting.

It worked for me.

On 8/1/06, xiaoyan <xiaoyezi.xyz_at_163.com> 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_at_gmail.com>
> *To:* xiaoyezi.xyz_at_163.com
> *Cc:* oracle-l_at_freelists.org
> *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_at_163.com> 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

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