请教个问题,我建了个触发器用来收集对表SCOTT.ORDERS
进行的UPDATE语句,但是建好之后,我运行一个UPDATE语句,会报错信息如下,就是无法抓取到UPDATE语句,这个是怎么回事呢?
报错信息:
UPDATE scott.ORders SET ID=612 WHERE ID=611
*
ERROR at line 1:
ORA-20001: ora_sql_txt未捕捉到任何语句.sql_txt未初始化
ORA-06512: at "SYS.TRIGGER_MONITOR_UPDATE_SQL", line 9
ORA-04088: error during execution of trigger 'SYS.TRIGGER_MONITOR_UPDATE_SQL'
触发器代码:
CREATE OR REPLACE TRIGGER TRIGGER_MONITOR_UPDATE_SQL
AFTER UPDATE ON SCOTT.ORDERS
DECLARE
NNUMBER;
STMT VARCHAR2(4000);
SQL_TEXT ORA_NAME_LIST_T;
BEGIN
DBMS_OUTPUT.PUT_LINE(ORA_SQL_TXT(SQL_TEXT));
N := ORA_SQL_TXT(SQL_TEXT);
IF NVL(N, 200) = 200 THEN
RAISE_APPLICATION_ERROR(-20001,
'ora_sql_txt未捕捉到任何语句.sql_txt未初始化');
ELSE
FOR I IN 1 .. N LOOP
STMT := STMT || SQL_TEXT(I);
END LOOP;
DBMS_OUTPUT.PUT_LINE(STMT);
END IF;
INSERT INTO MONITOR_SQL
(USERNAME, CLIENT_IP, SQL_TEXT, TABLE_NAME, OWNER)
VALUES
(USER, SYS_CONTEXT('userenv', 'ip_address'), STMT, 'T1', 'RAINY');
END;
|