We are using Oracle 10g on Linux platform. The following trigger is created to trace errors:
CREATE OR REPLACE TRIGGER test.error_log
AFTER SERVERERROR ON test.SCHEMA
DECLARE
v_errnum NUMBER;
v_counter NUMBER := 1;
v_errmsg VARCHAR2(1000); -- stack counter
v_errnumparam NUMBER;
v_errparam VARCHAR2(1000);
BEGIN
LOOP
-- get the error number off the stack
v_errnum := server_error (v_counter);
-- get the error number off the stack
v_errmsg := server_error_msg (v_counter);
-- if the error number is zero then exit.
v_errnumparam := server_error_num_params(v_counter);
v_errparam := server_error_param(v_counter, v_errnumparam);
EXIT WHEN v_errnum = 0;
-- commit is required because we are in an
-- autonomous transaction
INSERT INTO error_audit(SID, ORA_NUMBER, DESCRIPTION, STATUS, CREATED_BY_USER, CREATED_ON_DATE, MODIFIED_BY_USER, MODIFIED_ON_DATE)
VALUES (sys_context('USERENV','SID'),v_errnum, TRANSLATE(v_errmsg||';'||v_errnumparam||';'||v_errparam, 'x'||CHR(10)||CHR(13), 'x'), 'Y', USER, SYSDATE, USER, SYSDATE);
-- increment the counter and try again
v_counter := v_counter + 1;
END LOOP;
END;
The problem is that when we login from TEST schema, the below select statement logs error in the audit table. However, the following statement executed from SYSTEM user does not log the error. Can any body guide me to fix this error?
ALTER SESSION SET CURRENT_SCHEMA = test;
select * from error_audi
*
ERROR at line 1:
ORA-00942: table or view does not exist