Re: help about ora_sql_txt(sql_text)
- From: "xiaoyan" <xiaoyezi.xyz@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 2 Aug 2006 10:11:33 +0800
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?
Other related posts:
