Re: help about ora_sql_txt(sql

From: xiaoyan <xiaoyezi.xyz_at_163.com>
Date: Wed, 2 Aug 2006 09:59:05 +0800
Message-ID: <003901c6b5d7$3ce38e60$d88870ca@buaad538c81ca1>

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 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

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

Received on Tue Aug 01 2006 - 20:59:05 CDT

Re: help about ora_sql_txt(sql_text)