ORA-00604, ORA-04092, ORA-06512 and ORA-00942

I saw an ORA-00604 in a database recently and decided to see what might have caused it. The test below was run in an Oracle 9 database and shows one possible reason for an ORA-00604. Strangely enough, when I repeated the test in Oracle 10 and Oracle 11 databases, the ORA-00604 did not appear. If / when I find out why, I will update this post accordingly. First I ensured that _system_trig_enabled was set to true. You will see why at the end:

 

SQL> conn / as sysdba

Connected.

SQL> alter system

  2  set "_system_trig_enabled"=TRUE

  3  /

 

System altered.

 

SQL>

 

Then I created a user:

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL>

 

The user looked for a table which did not exist and got an ORA-00942 (as you do):

 

SQL> conn andrew/reid

Connected.

SQL> select * from tab1

  2  /

select * from tab1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL>

 

Next I created a user to own a trigger. This trigger is to fire on an after servererror on database event, write an error message to a table and commit it. To do this the user needs create trigger and administer database trigger privileges:  

 

SQL> conn / as sysdba

Connected.

SQL> create user fred identified by bloggs

  2  default tablespace users

  3  quota unlimited on users

  4  /

 

User created.

 

SQL> grant create session,

  2  create table,

  3  create trigger,

  4  administer database trigger

  5  to fred

  6  /

 

Grant succeeded.

 

SQL>

 

SQL> conn fred/bloggs

Connected.

SQL> create table failure_log

  2  (message varchar2(40))

  3  /

 

Table created.

 

SQL> create or replace trigger error_trigger

  2  after servererror on database

  3  begin

  4    insert into failure_log

  5    values('We have a problem');

  6    commit;

  7  end;

  8  /

 

Trigger created.

 

SQL>

 

You can identify these triggers as they have a trigger_type of AFTER EVENT:

 

SQL> select trigger_name, trigger_type

  2  from user_triggers

  3  /

 

TRIGGER_NAME         TRIGGER_TYPE

-------------------- ----------------

ERROR_TRIGGER        AFTER EVENT

 

SQL>

 

The next time Andrew tried to look for the table which did not exist, he got an ORA-00604 and an ORA-04092 as well because Fred’s trigger tried to do a commit:

 

SQL> conn andrew/reid

Connected.

SQL> select * from tab1

  2  /

select * from tab1

              *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04092: cannot COMMIT in a trigger

ORA-06512: at line 4

ORA-00942: table or view does not exist

 

SQL>

 

I set _system_trig_enabled to false to stop Fred’s trigger firing:

 

SQL> conn / as sysdba

Connected.

SQL> alter system

  2  set "_system_trig_enabled"=FALSE

  3  /

 

System altered.

 

SQL>

 

So the next time Andrew looked for the missing table, he just got an ORA-00942:

 

SQL> conn andrew/reid

Connected.

SQL> select * from tab1

  2  /

select * from tab1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL>