Re: help about ora_sql_txt(sql_text)

From: "xiaoyan" <xiaoyezi.xyz@xxxxxxx>
To: "xiaoyan" <xiaoyezi.xyz@xxxxxxx>
Date: Wed, 2 Aug 2006 16:30:08 +0800
Hi,
  Thank you very much,now I find I can get DML sql text through the function 
'current_sql' 
  ----- Original Message ----- 
  From: xiaoyan 
  To: oracle-l@xxxxxxxxxxxxx 
  Sent: Wednesday, August 02, 2006 10:11 AM
  Subject: Re: help about ora_sql_txt(sql_text)


  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@xxxxxxx ; 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@xxxxxxx>
    To: oracle-l@xxxxxxxxxxxxx
    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?
Re: help about ora_sql_txt(sql_text)

Other related posts:
