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

2 comments:

  1. Plaese to help me to find out a solution for the following mentioned error .
    " C:\>lsnrctl start
    LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-FEB-2013 18:13
    :17
    Copyright (c) 1991, 2005, Oracle. All rights reserved.
    Starting tnslsnr: please wait...
    TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
    System parameter file is E:\oracle\product\10.2.0\db_1\network\admin\listener.or
    a
    Log messages written to E:\oracle\product\10.2.0\db_1\network\log\listener.log
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc
    )))
    Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.4)(PORT=
    1521)))
    TNS-12545: Connect failed because target host or object does not exist
    TNS-12560: TNS:protocol adapter error
    TNS-00515: Connect failed because target host or object does not exist
    32-bit Windows Error: 49: Unknown error
    Listener failed to start. See the error message(s) above...
    "
    please kind me to help.

    ReplyDelete
    Replies
    1. You are not able to connect 192.168.1.4 on 1521 port.
      try to ping 192.168.1.4 first.
      and if you are getting ping then connect 192.168.1.4 on 1521 port using telnet.

      Delete