本人不才,刚学ORACLE不久,从网上下载了个oracle记录ip地址的触发器,稍加修改,调试通过。但在测试删除数据时,引发触发器错误。
line:3
ORA-06502:数字或值错误
ORA-06512:在‘DDL_WATCHER’,line 37
ORA-04088:触发器执行过程中出错附建表及触发器代码:CREATE table ddl_event (
timestamp date,
user_name varchar2(30),
os_user varchar2(30),
machine varchar2(20),
ip_addr varchar2(20),
program VARCHAR2(30),
event varchar2(20),
Object_name varchar2(30),
object_type varchar2(30),
object_owner varchar2(30),
statement varchar2(4000) )create or replace trigger ddl_watcher
after delete on test
--when (user not in ('SYS', 'SYSTEM'))
declare
v_osuser varchar2(30);
v_machine varchar2(20);
v_ip_addr varchar2(20);
v_program VARCHAR2(30);
event varchar2(30);
obj_name varchar2(30);
obj_type varchar2(30);
obj_owner varchar2(30);
sql_text ora_name_list_t;
stmt VARCHAR2(4000);
n number;
begin
select osuser,
machine,
nvl(program, 'sqlplus'),
sys_context('userenv','ip_address')
into
v_osuser,
v_machine,
v_program,
v_ip_addr
from v$session
where audsid = userenv('sessionid'); -- select sys_context('userenv','ip_address') into v_ip_addr from dual;
-- v_ip_addr := ora_client_ip_address; event := ora_sysevent;
obj_name := ora_dict_obj_name;
obj_type := ora_dict_obj_type;
obj_owner := ora_dict_obj_owner;/*
n := ora_sql_txt(sql_text);
if n > 256 then
n:= 256;
end if;
*/
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP; insert into ddl_event (timestamp, user_name, os_user, machine, ip_addr,
program, event, object_name, object_type, object_owner, statement)
values (sysdate, user, v_osuser, v_machine, v_ip_addr, v_program,
event, obj_name, obj_type, obj_owner, stmt);
end;
line:3
ORA-06502:数字或值错误
ORA-06512:在‘DDL_WATCHER’,line 37
ORA-04088:触发器执行过程中出错附建表及触发器代码:CREATE table ddl_event (
timestamp date,
user_name varchar2(30),
os_user varchar2(30),
machine varchar2(20),
ip_addr varchar2(20),
program VARCHAR2(30),
event varchar2(20),
Object_name varchar2(30),
object_type varchar2(30),
object_owner varchar2(30),
statement varchar2(4000) )create or replace trigger ddl_watcher
after delete on test
--when (user not in ('SYS', 'SYSTEM'))
declare
v_osuser varchar2(30);
v_machine varchar2(20);
v_ip_addr varchar2(20);
v_program VARCHAR2(30);
event varchar2(30);
obj_name varchar2(30);
obj_type varchar2(30);
obj_owner varchar2(30);
sql_text ora_name_list_t;
stmt VARCHAR2(4000);
n number;
begin
select osuser,
machine,
nvl(program, 'sqlplus'),
sys_context('userenv','ip_address')
into
v_osuser,
v_machine,
v_program,
v_ip_addr
from v$session
where audsid = userenv('sessionid'); -- select sys_context('userenv','ip_address') into v_ip_addr from dual;
-- v_ip_addr := ora_client_ip_address; event := ora_sysevent;
obj_name := ora_dict_obj_name;
obj_type := ora_dict_obj_type;
obj_owner := ora_dict_obj_owner;/*
n := ora_sql_txt(sql_text);
if n > 256 then
n:= 256;
end if;
*/
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP; insert into ddl_event (timestamp, user_name, os_user, machine, ip_addr,
program, event, object_name, object_type, object_owner, statement)
values (sysdate, user, v_osuser, v_machine, v_ip_addr, v_program,
event, obj_name, obj_type, obj_owner, stmt);
end;
create or replace trigger ddl_watcher
after delete on test
--when (user not in ('SYS', 'SYSTEM'))
declare
v_osuser varchar2(30);
v_machine varchar2(20);
v_ip_addr varchar2(20);
v_program VARCHAR2(30);
event varchar2(30);
obj_name varchar2(30);
obj_type varchar2(30);
obj_owner varchar2(30);
sql_text ora_name_list_t;
stmt VARCHAR2(4000);
n number;
begin
select osuser,
machine,
nvl(program, 'sqlplus'),
sys_context('userenv','ip_address')
into
v_osuser,
v_machine,
v_program,
v_ip_addr
from v$session
where audsid = userenv('sessionid'); -- select sys_context('userenv','ip_address') into v_ip_addr from dual;
-- v_ip_addr := ora_client_ip_address; event := ora_sysevent;
obj_name := ora_dict_obj_name;
obj_type := ora_dict_obj_type;
obj_owner := ora_dict_obj_owner;/*
n := ora_sql_txt(sql_text);
if n > 256 then
n:= 256;
end if;
*/
FOR i IN 1..ora_sql_txt(sql_text) LOOP--应该是你这里不对
stmt := stmt || sql_text(i);
END LOOP; insert into ddl_event (timestamp, user_name, os_user, machine, ip_addr,
program, event, object_name, object_type, object_owner, statement)
values (sysdate, user, v_osuser, v_machine, v_ip_addr, v_program,
event, obj_name, obj_type, obj_owner, stmt);
end;
这种trigger一般都是建在数据库系统级别的,用来监控数据库的,而不是像你这样随便建在一个表上面。
给你看下例子:
http://blog.csdn.net/xingfeng0501/article/details/6757857
FOR i IN 1..n LOOP <-------貌似是这里出错了,但不知道错误在哪?
stmt := stmt || sql_text(i);
END LOOP;好像的确是那里出错,但不知道问题在哪?