Re: help about ora_sql_txt(sql_text)

From: xiaoyan <xiaoyezi.xyz_at_163.com>
Date: Wed, 2 Aug 2006 10:11:33 +0800
Message-ID: <006601c6b5d8$faa35790$d88870ca@buaad538c81ca1>

Hi,
  Thank you very much,however,in Oracle 10.2,I got the ddl sql text through that way because the value of ora_sql_txt(sql_text) is 1 in ddl trigger,but when it comes to dml statement,the value of ora_sql_txt(sql_text) is null,which confused me greatly.

• Original Message -----

  From: Nigel Thomas
  To: xiaoyezi.xyz_at_163.com ; oracle-l
  Sent: Tuesday, August 01, 2006 11:55 PM   Subject: Re: help about ora_sql_txt(sql_text)

  Xiaoyan

  It seems ora_sql_text returns null on 10g - see http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1380003556171 and other google hits.

  sorry, that doesn't really answer your question, but it does explain why you get no SQL...

  Cheers

  Nigel

• Original Message ---- From: xiaoyan <xiaoyezi.xyz_at_163.com> To: oracle-l_at_freelists.org Sent: Tuesday, August 1, 2006 4:01:36 PM Subject: help about ora_sql_txt(sql_text)

  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?

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

Received on Tue Aug 01 2006 - 21:11:33 CDT