Re: help about ora_sql_txt(sql_text)

["xiaoyan" <xiaoyezi.xyz@xxxxxxx>]

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

• Follow-Ups:
  • Re: help about ora_sql_txt(sql_text)
    • From: Jared Still

• References:
  • help about ora_sql_txt(sql_text)
    • From: xiaoyan
  • Re: help about ora_sql_txt(sql_text)
    • From: Jared Still

Other related posts:

help about ora_sql_txt(sql_text)

Re: help about ora_sql_txt(sql_text)

Re: help about ora_sql_txt(sql_text)

Re: help about ora_sql_txt(sql_text)

Re: help about ora_sql_txt(sql_text)

Re: help about ora_sql_txt(sql_text)