Re: help about ora_sql_txt(sql_text)
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: xiaoyezi.xyz@xxxxxxx
- Date: Tue, 1 Aug 2006 08:51:43 -0700
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
Other related posts:
