ORA-00604: error occurred at recursive SQL level string

ORA-00604: error occurred at recursive SQL level string
Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).
Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28278/e0.htm#sthref17

While working on my last article on System Level Trigger I faced ORA-00604 with my database. Let me reproduce it again with a very simple example as following:

C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 27 11:54:31 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create user myuser identified by mypassword;
User created.

SQL> grant connect to myuser;
Grant succeeded.

SQL> grant resource to myuser;
Grant succeeded.

SQL> create or replace trigger user_logon_audit_trg
  2  after logon on database
  3  declare
  4     myevt varchar(10);
  5  begin
  6     if upper(sys_context('userenv', 'session_user')) <> 'SYS' then
  7             myevt := sys_context('userenv', 'session_user') || ' logged on at ' || to_char(sysdate,'dd-mon-yyyy');
  8     end if;
  9  end;
 10  /

Trigger created.

SQL> connect myuser/mypassword
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

Here ORA-00604 was thrown by the database because the "myevt" variable in "user_logon_audit_trg" trigger was not properly sized. I have seen ORA-00604 exception in Oracle database, usually by the system level triggers on DDL or SYSTEM events (Non DML). So we can simply try to debug them by finding relevant trigger from DBA_TRIGGERS by filtering triggering_event column.

ORA-00604 is usually a series of related messages issued at different levels of Oracle. The ORA-00604 error itself does not indicate the actual error. The message directly preceding ORA-00604 will list the reason for the error. If you are to able to find the possible reason/solution of ORA-00604, report it to Oracle Support Services by using your Metalink account.


Related Posts:
- ORA-01157: cannot identify/lock data file string - see DBWR trace file
- ORA-00849: SGA_TARGET cannot be set to more than MEMORY_MAX_TARGET
- ORA-27101: shared memory realm does not exist
- ORA-00600: internal error code
- ORA-00257: archiver error. Connect internal only, until freed
- ORA-16000: database open for read-only access