Hello all,
I’ve got a weird issue with an Oracle trigger trying to catch servererrors and log them. I can get scripts to force errors, but the sql reported error and the logged output do not match. Any ideas??
Here is my trigger:
create or replace TRIGGER ERROR_LOG_TRG
AFTER SERVERERROR on SCHEMA
Declare
Pragma AUTONOMOUS_TRANSACTION;
logged_by VARCHAR2(30) DEFAULT USER;
error_num NUMBER;
error_msg VARCHAR2(1000);
host VARCHAR2(50);
svc_name VARCHAR2(1000);
v_ErrorStack VARCHAR2(3000);
v_CallStack VARCHAR2(3000);
BEGIN
error_num := SQLCODE;
error_msg := sqlerrm;
IF error_num > 0 THEN
v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK;
v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK;
SELECT sys_context(‘USERENV’, ‘CURRENT_SCHEMA’) INTO logged_by FROM dual;
SELECT sys_context(‘USERENV’, ‘HOST’) INTO host FROM dual;
SELECT sys_context(‘USERENV’, ‘SERVICE_NAME’) INTO svc_name FROM dual;
INSERT
INTO error_log
(
error_log_id, logged_datetime, error_num, error_msg,error_stack, call_stack, logged_by, host, svc_name
)
VALUES
(
ERROR_LOG_ID_SEQ.NEXTVAL,SYSTIMESTAMP, error_num, error_msg,v_errorstack, v_callstack, logged_by, host, svc_name
);
COMMIT;
END IF;
END;
Here is my output:
SQL> select * from bad_table;
select * from bad_table
*
ERROR at line 1:
ORA-00942: table or view does not exist
Error logging. 0-ORA-0000: normal, successful completion
SQL>
Any ideas why I might be getting two different sets of messages?
Regards,
FG
