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.
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.
- 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