网上找了个例子用来捕获DML语句,全部代码如下:
create table t1(a number);
create table t_sql(username varchar2(30),
client_ip varchar2(20),sql_text varchar2(4000),
table_name varchar2(30),owner varchar2(30));CREATE OR REPLACE TRIGGER capt_sql
BEFORE DELETE OR INSERT OR UPDATE ON t1
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);
~~~~~~~~~~~~~~~~~~~~~~~~~~~
FOR i IN 1..nvl(n, 0) LOOP
stmt := stmt || sql_text(i);
END LOOP;
insert into t_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME,OWNER)
values(user,sys_context('userenv','ip_address'),stmt,'T1','morgan');
end;insert into t1 values(1);编译没问题,但是没法捕获DML语句,跟了一下发现ora_sql_txt(sql_text) 返回的值是NULL,系统版本是9i,请问是否得做什么系统设置或者打补丁之类的才能使ora_sql_txt(sql_text)返回的值不是空?
谢谢!!!
create table t1(a number);
create table t_sql(username varchar2(30),
client_ip varchar2(20),sql_text varchar2(4000),
table_name varchar2(30),owner varchar2(30));CREATE OR REPLACE TRIGGER capt_sql
BEFORE DELETE OR INSERT OR UPDATE ON t1
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);
~~~~~~~~~~~~~~~~~~~~~~~~~~~
FOR i IN 1..nvl(n, 0) LOOP
stmt := stmt || sql_text(i);
END LOOP;
insert into t_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME,OWNER)
values(user,sys_context('userenv','ip_address'),stmt,'T1','morgan');
end;insert into t1 values(1);编译没问题,但是没法捕获DML语句,跟了一下发现ora_sql_txt(sql_text) 返回的值是NULL,系统版本是9i,请问是否得做什么系统设置或者打补丁之类的才能使ora_sql_txt(sql_text)返回的值不是空?
谢谢!!!
n := ora_sql_txt(sql_text); 有值的呀,返回 n :=1;
我的数据库版本是9.2.0.7.0,而且可以找到sys.sql_txt这个系统函数
不知是否是因为版本的问题还是设置问题,希望遇到类似问题的大虾指点下,多谢了!!
USERNAME CLIENT_IP SQL_TEXT TABLE_NAME OWNER
------------------------------ -------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------
REDLIGHT 192.168.168.7 insert into t1 values(1) T1 morgan