|
想写个触发器监控谁对某张表做了dml操作。可以下面这个写法重视报错。报错的内容主要在 n := ora_sql_txt(sql_text); 请高手指点。
create table test_t(a number,b varchar2(20));
create table audit_dml_song (USERNAME VARCHAR2(30) ,
CLIENT_IP VARCHAR2(20),
SQL_TEXT VARCHAR2(4000) ,
TABLE_NAME VARCHAR2(30) );
create or replace trigger capt_sql
BEFORE DELETE OR INSERT OR UPDATE ON test_t
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
dbms_output.put_line('ora_name_list_t:' || to_char(n));
insert into audit_dml_song (USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME)
values(user,sys_context('userenv','ip_address'),stmt,'Test_t');
end;
/
|
|