Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SERVERERROR trigger does not fire from SYSTEM user

MoazzamMar 13 2013 — edited Mar 13 2013
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
This post has been answered by Solomon Yakobson on Mar 13 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 10 2013
Added on Mar 13 2013
7 comments
767 views