Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Piotr.

Asked: May 24, 2002 - 8:09 am UTC

Last updated: July 01, 2013 - 7:13 pm UTC

Version: 9.0.1.0.0

Viewed 100K+ times! This question is

You Asked

Hi,

I have the following problem:

SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 12
ORA-00942: table or view does not exist

And table `t' does not exist.
But this happens no matter what user I am, no matter what I do but only if there are errors (assuming that it would normally give only the last error).

I was looking for the trigger that might do commit but there is no single one in any schemas in our database.

What does this ORA-00604 mean?
Does it have something to do with recursive SQL queries?
Is this some kind of internal problem?
How can I get rid of it?

Thanx in advance,
Piotr


and Tom said...

someone created an "after servererror" trigger and is committing in it. consider:

ops$tkyte@ORA9I.WORLD> drop user a cascade;

User dropped.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> grant create session to a identified by a;

Grant succeeded.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> @connect a/a
ops$tkyte@ORA9I.WORLD> set termout off
a@ORA9I.WORLD> REM GET afiedt.buf NOLIST
a@ORA9I.WORLD> set termout on
a@ORA9I.WORLD> select * from t;
select * from t
*
ERROR at line 1:
ORA-00942: table or view does not exist


a@ORA9I.WORLD>
a@ORA9I.WORLD> @connect /
a@ORA9I.WORLD> set termout off
ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST
ops$tkyte@ORA9I.WORLD> set termout on
ops$tkyte@ORA9I.WORLD> drop table log;

Table dropped.

ops$tkyte@ORA9I.WORLD> create table log( msg varchar2(255) );

Table created.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> create or replace trigger error_trigger
2 after servererror on database
3 begin
4 insert into log values ( 'hi' );
5 commit;
6 end;
7 /

Trigger created.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> @connect a/a
ops$tkyte@ORA9I.WORLD> set termout off
a@ORA9I.WORLD> REM GET afiedt.buf NOLIST
a@ORA9I.WORLD> set termout on
a@ORA9I.WORLD> select * from t;
select * from t
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at line 3
ORA-00942: table or view does not exist


a@ORA9I.WORLD>
a@ORA9I.WORLD> @connect /
a@ORA9I.WORLD> set termout off
ops$tkyte@ORA9I.WORLD> REM GET afiedt.buf NOLIST
ops$tkyte@ORA9I.WORLD> set termout on
ops$tkyte@ORA9I.WORLD> select owner, trigger_name
2 from dba_triggers
3 where trigger_type = 'AFTER EVENT';

OWNER TRIGGER_NAME
------------------------------ ------------------------------
SYS AURORA$SERVER$STARTUP
OPS$TKYTE ERROR_TRIGGER

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> drop trigger error_trigger;

Trigger dropped.



Use that last query to find the offending trigger and either make it an autonomous transaction so it can commit or remove the commit.

Rating

  (48 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It just takes to know right things

Piotr Jarmuz, May 24, 2002 - 11:56 am UTC

Now all is clear. I understand the flow of locus of control. Actually I knew about system level triggers but not about `after servererror' so actually I could not associate it with this message. I used an autonomous transaction and now it all works well. And I also traced down the culprit who or rather what set this trigger up.

Thank you again.

can commit cause problem in Logoff trigger

Dilip, July 09, 2002 - 11:40 am UTC

In before logoff trigger, I am updating flag like this:

..before logoff on database..

If <condition> THEN
Update table
set col = releaseflag ;
==> COMMIT ;
End if ;
..
Can Commit in this trigger cause the database to shutdown?
Thanks!
DilipM

Tom Kyte
July 09, 2002 - 12:45 pm UTC

it should not.

What about Forms 6i

Luis Soto, January 03, 2003 - 10:08 am UTC

In forms 6i every time I shutdown and restart Personal Oracle 8.1.5 in Win98Se Forms give me this error when I try to commit my chages. I tried it in SQL*Plus and it Works well. If I compile the form again it runs well. What drive me crazy is that it happen just with one form the others works well.

Any Help Will be really apreciate


Luis Soto

superb answer for after servererror

jignesh, May 07, 2003 - 11:45 am UTC

u r simply gr8

ORA-00604 while compiling a procedure

Mariano, January 22, 2004 - 6:59 pm UTC

Hi Tom,
while trying to compile a procedure it's raising this error:
ORA-00604: error occurred al recursive sql level 1
ORA-01653: unable to extend table SYS.IDL_UB2$ by 25 in tablespace SYSTEM.
I cannot query this table/view.
how can i now if something in my code is causing the recursion (I mean a guide in this matter as i'm aware that without taking a look in the code it's imposible to know for sure) or it's necesary to ask my DBA to check tablespace system in order verify extends?
Just to mention: i'm not calling the procedure inside its own code but i'm calling another procedure with the same error.

As usual, thanks in advance.

Tom Kyte
January 22, 2004 - 8:31 pm UTC

contact your dba.

system is full, idb_ub2$ is a "byte code" table. dba needs to add space

ORA-00604 while dropping table

A reader, June 06, 2004 - 6:28 pm UTC

Tom I am getting the following error..

drop table t_emp_varray;
drop table t_emp_varray
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: table "USER1.T_EMP_VARRAY" has errors

Any ideas? Is there a way to "force" the dropping
of the table?

thanx!

Tom Kyte
June 07, 2004 - 7:59 am UTC

give me a little "info" -- how did you get into this condition?

thanx Tom!

A reader, June 07, 2004 - 11:14 am UTC

I had been executing a script - I must have done
something and this condition started showing up
this is 10gr1.
My hunch is it has to do with the "force" option
in dropping the type.

Following is the script..

scott@ORA10G> /*
scott@ORA10G> drop objects.
scott@ORA10G> */
scott@ORA10G> drop table t_emp_varray;
drop table t_emp_varray
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: table "BENCHMARK.T_EMP_VARRAY" has errors


scott@ORA10G> drop type emp_type force;

Type dropped.

scott@ORA10G> drop type emp_type_varray force;

Type dropped.

scott@ORA10G>
scott@ORA10G> /* create an object type - emp_type */
scott@ORA10G> create or replace type emp_type as object
2 (
3 name varchar2(30),
4 hire_date date
5 );
6 /



Tom Kyte
June 07, 2004 - 1:16 pm UTC

hows about the entire example? I cannot think of anyway to reproduce.

Annie, July 16, 2004 - 3:22 am UTC

We're facing a similar problem. When data is entered through Forms 6i and saved, "FRM-40508-ORACLE error unable to insert record" occurs. When the error is viewed,
The SQL statement in error is something like
insert into cust_rect values(:1,:2)
Error:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist.

But the table cust_rect exists.

Please help.




Tom Kyte
July 16, 2004 - 11:07 am UTC

must not exist for the currently logged in user then.

Error Dropping User

Thiyagu, August 24, 2005 - 10:54 am UTC

Tom:

I am trying to drop a user using the command:

drop user xyz cascade;

and this gives me an error

ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

I used the query you had supplied in the earlier comment (
select owner, trigger_name
from dba_triggers
where trigger_type = 'AFTER EVENT';) which does not return me any results. I had deleted all the after event triggers.

Still the error seem to exist. Can you please comment.

Tom Kyte
August 24, 2005 - 5:58 pm UTC

turn on sql_trace=true and see what statement is failing in the trace file

Please ignore my earlier comment

Thiyagu, August 24, 2005 - 3:25 pm UTC

I have upgraded the database from 9.2.0.1 to 9.2.0.5 and did not complete the upgrade with the Post Installation tasks. After I execute the post installation task all worked fine.

Raghav, December 09, 2005 - 3:11 am UTC

Hi Tom

First case: For one user when I am trying to connect to the database through his username and password, I am getting the following error.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 20
ORA-06512: at line 2

I have granted that user with create session, connect, resource. But still not able to connect.

Second Case: For a particular user when he is trying to refresh the cubes (there are 10 cubes) at one cube this particular error was coming. whereas he is able to connect to the database through his login and as well he is able to refresh rest 9 cubes.
ERROR: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 17
ORA-06512: at line 2

Request you to please give some light on these issues.

Thanks and Regards
Raghav

Tom Kyte
December 09, 2005 - 6:47 am UTC

case 1: has NOTHING to do with the user

this means that the context you are trying to set on line 20 of the app_security_context procedure was not created in a way that allows app_security_context to set it.

EVERY user that runs app_security_context will recieve this same error.


case 2: insufficient data to even begin to comment.

Recursive SQL

Raghav, December 09, 2005 - 3:12 am UTC

Hi Tom

First case: For one user when I am trying to connect to the database through his username and password, I am getting the following error.

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 20
ORA-06512: at line 2

I have granted that user with create session, connect, resource. But still not able to connect.

Second Case: For a particular user when he is trying to refresh the cubes (there are 10 cubes) at one cube this particular error was coming. whereas he is able to connect to the database through his login and as well he is able to refresh rest 9 cubes.
ERROR: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 17
ORA-06512: at line 2

Request you to please give some light on these issues.

Thanks and Regards
Raghav

Debugging Cancellation Error

Smita, December 09, 2005 - 9:59 am UTC

Hi Tom,

I get the following errors when I cancel a debugging session(procedures, packages, etc) in toad.

ORA-00604
ORA-01422
ORA-06512
ORA-O6543

Please help.

Tom Kyte
December 09, 2005 - 12:59 pm UTC

guess I'd give toad a call and ask them "so, whats up with that?"??? not sure what else to say - given I don't have toad myself and have never used it.

numeric or value error

Raghav, December 10, 2005 - 2:11 am UTC

Hi Tom

We have a system called cpms in which the data is retrieved into excel in the form of cubes for analysis purpose. The data/cubes are refreshed in ones pc when he refreshed the reports with his userid (database) and password. These users (database) are given the cpms_reports 'role' permission, whcih allows the them to connect to cpms through their userid and password to access these reports. While all the other users are able to refresh all the cubes, one particular user particularly in one cube is getting this error. He was able to refresh the other 9 cubes.

ERROR: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "CPMS.APP_SECURITY_CONTEXT", line 17
ORA-06512: at line 2

Hope this information will give you some idea to understand and to analyse the problem and give me a solution.

Thanks and regards
Raghav


Tom Kyte
December 10, 2005 - 5:24 am UTC

I guess you have data in strings that cannot be converted to a number and only this user it hitting that data, others are not using that particular bad piece of data.

still entirely insufficient data to diagnose anything. Very similar to me going into a car repair shop and saying:

I have a car that I drive on highway 101 at 65mph for 2 hours every day. I turn right then I turn left. I park the car.

Now, the car won't start. why not?




Numeric or value error

Raghav, January 28, 2006 - 2:03 am UTC

Hi Tom

Sorry for the delayed reply. The app_security_context package is given below which has given the error. We have identified the problem as the predicate is giving the error. It is returning more than 100 characters. We have increased it to 500 and the problem got resolved.

PACKAGE App_Security_Context IS
PROCEDURE Set_Login_Context;
END;
PACKAGE BODY App_Security_Context IS
PROCEDURE Set_Login_Context
IS
Login_Name varchar2(15);
LOC varchar2(15);
PRED varchar2(100);
PRED2 varchar2(100);
CURSOR C1(login varchar2) IS SELECT LOCATION_NAME FROM CPMS.TBL_ROLE_LOCATION_MAPPING TRLM,
CPMS.tbl_Location_Master TLM WHERE TLM.Location_ID=TRLM.Location_ID AND TRLM.Role=login;

BEGIN
Login_Name := SYS_CONTEXT('USERENV','SESSION_USER');
OPEN C1(Login_Name);
LOOP
FETCH C1 INTO LOC;
EXIT WHEN C1%NOTFOUND;
PRED := PRED || ' Circle=''' || LOC || ''' OR ';
PRED2 := PRED2 || ' Buying_Circle=''' || LOC || ''' OR Sourcing_Circle
=''' || LOC || ''' OR ';

END LOOP;
PRED := SUBSTR(PRED,1,LENGTH(PRED)-3);
PRED2 := SUBSTR(PRED2,1,LENGTH(PRED2)-3);
CLOSE C1;
DBMS_SESSION.SET_CONTEXT('CPMS_CONTEXT', 'PREDICATE', PRED);
DBMS_SESSION.SET_CONTEXT('CPMS_CONTEXT', 'PREDICATE2', PRED2);
END;
END;

Here the system is throwing the numetic or value error instead of giving a message like "the value too long" or "over flow" which would have given a clear understanding of the problem. Your coments please.


Tom Kyte
January 28, 2006 - 12:58 pm UTC

the numeric or value error is precisely what you get when you have a numeric or value error (value error being "string is slightly too long")

In current software, the message is more verbose:

ops$tkyte@ORA10GR1> declare
  2          x varchar2(1);
  3  begin
  4          x := 'a' || 'b';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4


but not too much different from:

ops$tkyte@ORA9IR2> declare
  2          x varchar2(1);
  3  begin
  4          x := 'a' || 'b';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 4


 

Similar Error

Aryan, August 23, 2006 - 4:50 am UTC

Hi Tom,

Can you please tell us when does oracle throws error like

ORA-00604:Error Occurred at recursive SQL level 1
ORA-02067: Transaction or Savepoint rollback required

I came across this error when i was deleting a record from WebPage which has Oracle as backend.

Thanks,



Tom Kyte
August 27, 2006 - 3:22 pm UTC

[tkyte@desktop ~]$ oerr ora 2067
02067, 00000, "transaction or savepoint rollback required"
// *Cause: A failure (typically a trigger or stored procedure with multiple
// remote updates) occurred such that the all-or-nothing execution
// of a previous Oracle call cannot be guaranteed.
// *Action: rollback to a previous savepoint or rollback the transaction
// and resubmit.

that is "when", now you need to look at the code to see "why"

ORA_00604 error occurred at recursive SQL level 1

Anjali, October 18, 2006 - 2:25 pm UTC

I have this error when I shutdown immediate I get ora 00604 along with ora 00904 invalid column. This is window 2003 and 8.1.7 version of oracle. I know is old but this is the government.
How should I correct the databse I refreshed it started it fine but now this error comes for everything when I view the tables or the shut the database I have to shut only abort. I think it is missing some oracle files could you please help.

Tom Kyte
October 18, 2006 - 3:44 pm UTC

you probably have a "shutdown" trigger and it is failing.


look at your installed set of triggers....

Vishal, February 18, 2008 - 5:42 am UTC

Hi tom,
my application is on .net,c#,oracle 10g.here i am facing a issue.i have created a procedure,which is simply returing a refcursor. ->

--------------
CREATE OR REPLACE Procedure spn_LinesTooling(tmptable in varchar2,p_stmt1 out sys_refcursor) is
stmt varchar2(32000);
v_table varchar2(32000);
Begin
v_table := tmptable;
stmt := 'select * from '||v_table||' order by decode(subsystem,''TOTAL'',''ZZZ2'','' '',''ZZZ1'',subsystem),tipo ';
Open p_stmt1 for stmt;
exception
when others then
err('error in LinesTooling '|| sqlerrm);
commit;
End spn_LinesTooling;
--------------------------


it is created fine and running fine on Sqlpromt.

var p refcursor;
declare
begin
spn_LinesTooling('temp_table',:p);
end;
/
PL/SQL procedure successfully completed.

but while calling from front end ,c#,.Net.
it is giving a error.

ORA-00604: error occurred at recursive SQL no statement parsed

Please help it is very urgent.

Vishal



Tom Kyte
February 18, 2008 - 7:57 am UTC

for the love of whatever...


 exception
              when others then
             err('error in LinesTooling '|| sqlerrm);
                  commit;
End spn_LinesTooling;



WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY WHY



http://www.google.com/search?q=site%3Atkyte.blogspot.com+%22when+others+null%22&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

Your approach is even worse than normal.

Hey - I know, when we get some unknown error, we'll just COMMIT.

Please - tell me you work on nothing anyone uses in real life, because this is just scary. Just totally scary. Unbelievably scary. Frightening.

I cannot say how bad this is, it is the worst of the worst.


when any error
   then - just commit whatever else has happened



I hope you do not work for any financial institution I keep money in.


a) remove $500 from checking
b) call your procedure - whoops, error, commit
c) fail adding $500 to savings

bummer, just lost 500$ since rollback doesn't quite work anymore.


what is urgent is that you immediately, now, today, stop new development and go back to every bit of code that has WHEN OTHERS

and make sure that

a) it does NOT commit

b) you verify that it actually makes sense to even have it (hint: in your case above, no, it does not, just erase it NOW)

c) upon verifying that it in fact does make sense, that it invokes either RAISE or RAISE_APPLICATION_ERROR to re-raise the exception.


You are hitting some error, your exception block is invoked. You commit the work, you return to the client. the client is entirely clueless that an ERROR HAS OCCURRED, entirely clueless, you hid it from them. And client gets ora-604 when it fetches from a cursor that is not open.


erase your when others and it will become obvious what the real issue is. Funny, when you call it from sqlplus, it is probably FAILING JUST AS MISERABLY, you just don't know it because of this when others.

Oh, let us not forget also, this is subject to SQL INJECTION, you are not validating your inputs...


ERR() could be an autonomous transaction. The code *could* look like this:

ops$tkyte%ORA10GR2> create table error_log
  2  ( dt      timestamp,
  3    errno   number,
  4    errmsg  varchar2(4000),
  5    stack   varchar2(4000)
  6  );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure log_error( p_sqlcode in number, p_sqlmsg in varchar2 )
  2  as
  3      pragma autonomous_transaction;
  4      l_stack varchar2(4000) := substr( dbms_utility.format_error_backtrace, 1, 4000 );
  5  begin
  6      insert into error_log( dt, errno, errmsg, stack )
  7      values ( systimestamp, p_sqlcode, substr( p_sqlmsg, 1, 4000 ), l_stack );
  8      commit;
  9  end;
 10  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE OR REPLACE
  2  Procedure spn_LinesTooling
  3  (p_tname in  varchar2,
  4   p_stmt1 out sys_refcursor
  5  )
  6  is
  7  Begin
  8      open p_stmt1 for
  9      'select *
 10         from '  || dbms_assert.SIMPLE_SQL_NAME( p_tname ) || '
 11       order by decode(subsystem,''TOTAL'',''ZZZ2'','' '',''ZZZ1'',subsystem), tipo';
 12  exception
 13  when others
 14  then
 15      log_error( sqlcode, sqlerrm );
 16      RAISE;
 17  End spn_LinesTooling;
 18  /

Procedure created.

ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec spn_linestooling( 't', :x );
BEGIN spn_linestooling( 't', :x ); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "OPS$TKYTE.SPN_LINESTOOLING", line 15
ORA-06512: at line 1


ops$tkyte%ORA10GR2> select * from error_log;

DT                             ERRNO ERRMSG               STACK
------------------------- ---------- -------------------- --------------------
18-FEB-08 08.22.01.124731       -942 ORA-00942: table or  ORA-06512: at
 AM                                  view does not exist  "OPS$TKYTE.SPN_LINES
                                                          TOOLING", line 7


ops$tkyte%ORA10GR2> create table t ( subsystem varchar2(30), tipo varchar2(30) );

Table created.

ops$tkyte%ORA10GR2> exec spn_linestooling( 't', :x );

PL/SQL procedure successfully completed.



but you must, now - immediately - go back and revisit every bit of code, looking for when others. You have done it entirely wrong, in the worst of ways.


Failure line number preservation

David Horton, February 18, 2008 - 5:03 pm UTC

Hi Tom,

I'm pretty sure I know the answer to this but just want to clarify to check my understanding.

If you use the autonomous transaction approach to log and re-raise the exception, the original line number that failed is not passed back to the client in the exception stack, correct? It will be the line of the raise that's passed back.

So, if you use this approach, you have to make sure to log the original stack trace, as you've done in your response, in order to be able to identify which line cause the original error. Correct?

Is there any way of accessing the original "nested" exception after a raise? Something like a "caused by".

Thanks
Tom Kyte
February 18, 2008 - 6:38 pm UTC

if you catch it, you change it.

you can certainly stuff the error stack into a plsql global variable for access by the client...

That code was scary

John, February 21, 2008 - 10:48 am UTC

But I can't tell you how many people code like this. It keeps me up at night.
Tom Kyte
February 21, 2008 - 11:31 am UTC

it causes me a great deal of stress - some mornings I just want to shout (or cry, not sure which). Seeing it over and over and over again just makes me feel really bad.

sort of depressing.

Unable to drop table

Sikki, May 14, 2008 - 12:24 pm UTC

Hi Tom,

I am unable to drop any table in the database

Version - 10.1.0.3

SQL> create table test (a number);

Table created.

SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges

Any idea? Am I hitting the Bug?

Tom Kyte
May 14, 2008 - 4:05 pm UTC

"the bug", no - not "the bug", "the bug" is a horrible bug, if you hit it - your database would self destruct. this is not "the bug" ;)


It is not a bug either, at least not with Oracle.

It is a bug in a trigger you or your coworkers created.


Look for a before event trigger in your database, someone created one I bet. It is firing on the drop of objects and it is doing something it is not allowed to do.

Alexander the ok, May 14, 2008 - 4:17 pm UTC

Laughing out loud. I am having the worst week, I needed that.

Rahul, May 15, 2008 - 12:52 pm UTC

>>Laughing out loud. I am having the worst week, I needed that.

Whats so funny about that?

Hit the bug ??

A reader, May 15, 2008 - 1:49 pm UTC

You don't get it do you ? It's Tom's great sense of humor.

Unable to drop table

sikki, May 20, 2008 - 4:13 am UTC

Hi Tom,

Thanks for your review and suggestion. No event trigger was causing issue. I found one hidden parameter in metalink and applied, it worked.

SQL> drop table drop_test;

drop table drop_test

ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges


SQL> alter system set "_system_trig_enabled"=TRUE;

System altered.

SQL> drop table test;

Table dropped.

Tom Kyte
May 20, 2008 - 11:21 am UTC

umm, the magic parameter you just referenced is all about system event triggers...

Cant drop a user after oracle upgrade

Ivan, July 03, 2008 - 3:50 am UTC

Hi Tom,

I had recently installed oracle9i and upgrade it to 9.2.0.7, with all the steps done. But when I created a user for testing purpose and trying to drop the user afterward, I encounter an error...

SQL> drop user ranger cascade
  2  ;
drop user ranger cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7

when I used your query to find the error trigger.. i got the following return.

SQL> select owner, trigger_name
  2   from dba_triggers
  3  where trigger_type = 'AFTER EVENT';

OWNER                          TRIGGER_NAME
------------------------------ ------------------------------
SYS                            AURORA$SERVER$STARTUP
SYS                            AW_DROP_TRG
SYS                            CDC_CREATE_CTABLE_AFTER
SYS                            SDO_DROP_USER
SYS                            SYS_LOGON

Please advise.

Tom Kyte
July 07, 2008 - 9:40 am UTC

turn on sql_trace and see what is happening.

ORA-00604: error occurred at recursive SQL level 3

Nantha, November 10, 2008 - 6:36 pm UTC

Hi Tom,

We have a procedure which has the output parameter as refcusor type, When we execute the procedure with some user it works fine, when it is executed with an application user we are getting the following error,

error signaled in parallel query server P001
ORA-00604: error occurred at recursive SQL level 3
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 51
The application user has all rights as we have for other users.
When I ran the same proc in pl/sql developer debug mode, the execution went fine but when I see the cursor result getting "ORA-01002 Fetch out of Sequence".

Could you please let me know what exactly we are missing here?
Thanks
Nantha

Tom Kyte
November 11, 2008 - 3:57 pm UTC

well, a test case for starters...

Create database error

Saraf, November 14, 2008 - 3:59 am UTC

I am trying to create a database .and when i try to using below mentioned parameters i get an error 
sql>startup nomount pfile=c:\OVSD\pfile\ovsd.ora
ORACLE instance started.
Total System Global Area   97589952 bytes
Fixed Size                   453312 bytes
Variable Size              46137344 bytes
Database Buffers           50331648 bytes
Redo Buffers                 667648 bytes
SQL> create database ovsd
  2  logfile group 1 'c:\OVSD\log\redo1.ree' size 2m,
  3  group 2 'c:\OVSD\log\redo2.ree' size 2m
  4  datafile 'c:\OVSD\data\system.dbf' size 5m
  5  undo tablespace ut
  6  datafile 'c:\OVSD\data\undo.dbf' size 8m;
create database ovsd
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Tom Kyte
November 18, 2008 - 5:41 pm UTC

read the alert log. tell us what you see.

it'll probably be an unreasonable setting for memory or processes.

alert log attached

Saraf, November 14, 2008 - 4:00 am UTC

Fri Nov 14 12:21:15 2008
create database ovsd
logfile group 1 'c:\OVSD\log\redo1.ree' size 2m,
group 2 'c:\OVSD\log\redo2.ree' size 2m
datafile 'c:\OVSD\data\system.dbf' size 5m
Fri Nov 14 12:21:15 2008
Database mounted in Exclusive Mode.
Fri Nov 14 12:21:16 2008
Successful mount of redo thread 1, with mount id 3894573755.
Assigning activation ID 3894573755 (0xe8227abb)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: C:\OVSD\LOG\REDO1.REE
Successful open of redo thread 1.
Fri Nov 14 12:21:16 2008
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Nov 14 12:21:16 2008
SMON: enabling cache recovery
Fri Nov 14 12:21:16 2008
WARNING: Default passwords for SYS and SYSTEM will be used.
Please change the passwords.
Fri Nov 14 12:21:16 2008
Errors in file c:\ovsd\trace\user\ovsd_ora_3072.trc:
ORA-01501: CREATE DATABASE failed
ORA-30045: No undo tablespace name specified

Error 30045 happened during db open, shutting down database
USER: terminating instance due to error 30045
Instance terminated by USER, pid = 3072
ORA-1092 signalled during: create database ovsd
logfile group 1 'c:\OVSD\log\...
Tom Kyte
November 18, 2008 - 5:44 pm UTC

what did your pfile have in it?

Karady, April 24, 2009 - 12:12 pm UTC

This is the content of logon trigger .


CREATE OR REPLACE TRIGGER LOGON_TRIGGER AFTER
LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set nls_date_format = "MM/DD/YYYY"';
EXECUTE IMMEDIATE 'alter session set nls_timestamp_format = "yyyy-MM-dd HH24:MI:SS"';
EXECUTE IMMEDIATE 'alter session set nls_sort = "BINARY"';
EXECUTE IMMEDIATE 'alter session set nls_comp = "BINARY"';
EXECUTE IMMEDIATE 'alter session set use_stored_outlines = "CACHE_QUERY"';
END;


In the alert log , I see this entry.
What is the possible reason for this error (As I see , There is no need for 'COMMIT' . Please correct me , if I am wrong)
?


Skipped error 604 during the execution of LOGON_TRIGGER
*** 2009-04-23 17:50:04.583
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-00922: missing or invalid option
ORA-06512: at line 2

Tom Kyte
April 27, 2009 - 12:30 pm UTC


  1  CREATE OR REPLACE TRIGGER LOGON_TRIGGER AFTER
  2  LOGON ON DATABASE
  3  BEGIN
  4          EXECUTE IMMEDIATE 'alter session set nls_date_format = "MM/DD/YYYY"';
  5          EXECUTE IMMEDIATE 'alter session set nls_timestamp_format = "yyyy-MM-dd HH24:MI:SS"';
  6          EXECUTE IMMEDIATE 'alter session set nls_sort = "BINARY"';
  7          EXECUTE IMMEDIATE 'alter session set nls_comp = "BINARY"';
  8          EXECUTE IMMEDIATE 'alter session set use_stored_outlines = "CACHE_QUERY"';
  9* END;
ops$tkyte%ORA10GR2> /





that worked for me. Please verify you are posting the actual code.

ops$tkyte%ORA10GR2> column trigger_body format a200
ops$tkyte%ORA10GR2> set long 5000
ops$tkyte%ORA10GR2> set linesize 220
ops$tkyte%ORA10GR2> select trigger_body from user_triggers where trigger_name = 'LOGON_TRIGGER';

TRIGGER_BODY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
        EXECUTE IMMEDIATE 'alter session set nls_date_format = "MM/DD/YYYY"';
        EXECUTE IMMEDIATE 'alter session set nls_timestamp_format = "yyyy-MM-dd HH24:MI:SS"';
        EXECUTE IMMEDIATE 'alter session set nls_sort = "BINARY"';
        EXECUTE IMMEDIATE 'alter session set nls_comp = "BINARY"';
        EXECUTE IMMEDIATE 'alter session set use_stored_outlines = "CACHE_QUERY"';
END;



A reader, April 27, 2009 - 2:50 pm UTC

Tom -

This is the actual code . The only thing I have changed here is the userid to XXXXXX.

Thanks

<code>SQL> column trigger_body format a200
SQL> set long 5000
SQL> set linesize 220
SQL> select trigger_body from user_triggers where trigger_name = 'LOGON_TRIGGER';


TRIGGER_BODY
------------------------------------------------------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE 'alter session set nls_date_format = MM/DD/YYYY';
EXECUTE IMMEDIATE 'alter session set nls_timestamp_format = yyyy-MM-dd HH24:MI:SS';
EXECUTE IMMEDIATE 'alter session set nls_sort = BINARY';
EXECUTE IMMEDIATE 'alter session set nls_comp = BINARY';
EXECUTE IMMEDIATE 'alter session set use_stored_outlines = CACHE_QUERY';
EXECUTE IMMEDIATE 'ALTER SESSION SET query_rewrite_enabled=TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET use_stored_outlines=MERGE_OUTLINES';

END;


SQL>



tail utdb1_j000_32575.trc


Mutex 0x2deac5638(84, 0) idn 0 oper EXAM
Cursor Parent uid 84 efd 26 whr 26 slp 0
oper=DEFAULT pt1=(nil) pt2=(nil) pt3=(nil)
pt4=(nil) u41=0 stt=0
Skipped error 604 during the execution of XXXXXX.LOGON_TRIGGER
*** 2009-04-27 14:27:57.250
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-00922: missing or invalid option
ORA-06512: at line 2</code>

Tom Kyte
April 27, 2009 - 3:02 pm UTC

just cut and paste your trigger into sqlplus:

ops$tkyte%ORA10GR2> BEGIN
  2      EXECUTE IMMEDIATE 'alter session set nls_date_format = MM/DD/YYYY';
  3      EXECUTE IMMEDIATE 'alter session set nls_timestamp_format = yyyy-MM-dd HH24:MI:SS';
  4      EXECUTE IMMEDIATE 'alter session set nls_sort = BINARY';
  5      EXECUTE IMMEDIATE 'alter session set nls_comp = BINARY';
  6      EXECUTE IMMEDIATE 'alter session set use_stored_outlines = CACHE_QUERY';
  7      EXECUTE IMMEDIATE 'ALTER SESSION SET query_rewrite_enabled=TRUE';
  8      EXECUTE IMMEDIATE 'ALTER SESSION SET use_stored_outlines=MERGE_OUTLINES';
  9
 10  END;
 11  /
BEGIN
*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at line 2




did you notice that your actual code DIFFERS from what you posted entirely? (hint - see any "double quotes" in the trigger body??)

Sorry about that .

A reader, April 27, 2009 - 3:23 pm UTC

Thanks a lot .

I have been staring at that for a while . Did not pay attention to the quotes ... Thanks again !

A reader, August 09, 2009 - 4:51 pm UTC

I am getting the following error in executing while executing the "dropping the trigger " , "creating a trigger " . or executing some of the stored proc . 
Please advise . 

Is there any thing I should look for . 


I am on 10g Release 2 on Redhat linux .
SQL> drop trigger logon_trigger ;

drop trigger logon_trigger

ORA-00604: error occurred at recursive SQL level 1
ORA-01008: not all variables bound
ORA-06512: at line 21

Tom Kyte
August 11, 2009 - 1:37 pm UTC

look for a DDL trigger that you or your coworkers have created.

Looks like they have a "on drop" trigger that is capturing the trigger you are dropping and doing some dynamic sql using it - but since it contains :new.column name - they are confusing something somewhere

(meaning they are not using binds themselves! meaning you have a HUGE security issue in your database - google:

sql injection
)


A reader, August 09, 2009 - 6:54 pm UTC

I was able to resolve this by setting a undocumented parameter "_system_trig_enabled"=FALSE .
But , I have a logon trigger . If I set this to FALSE , logon trigger does not kick in .

How to have a logon trigger working and to avoid this error ?


Note : This has been working for a long time ( atleast 6 - 7 months )
Tom Kyte
August 11, 2009 - 1:38 pm UTC

ummm, come on - you guys WROTE THE CODE

it is your code that is failing, on line 21

review your code to determine what bug you have in it.

A reader, August 16, 2009 - 12:15 pm UTC

Tom Kyte
August 24, 2009 - 8:10 am UTC

interesting, in that it has nothing at all to do with the problem you described.

I fail completely to see how this affected anything. You have a trigger (on drop) you wrote, it is failing - you have a bug in it. Nothing more, nothing less.

error

A reader, May 14, 2010 - 10:02 am UTC


Maximum open cursors exceeded

sasi, December 10, 2010 - 12:17 am UTC

Dear Tom,

i am using stored procedure in Oracle and the procedure is returning one cursor.i though whenever we close callable statement and result cursor also automatically closed but its not closed. after performing number of iterations system is throughing maximum no of cursors are exceeded.
after executing the cursor even i am closing connection also,then also its not closing.how to closed the cursor explicitly.

Tom Kyte
December 10, 2010 - 9:59 am UTC


If you are getting a ref cursor back - YOU are responsible for closing that ref cursor. Ref cursors are not closed when you close the stored procedures callable statement - they cannot be, it would not make sense, they are totally independent of each other.

Sudhir, December 28, 2010 - 8:45 am UTC

While Compiling a procedure i got the same type of Error

Error report:
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME")
00604. 00000 - "error occurred at recursive SQL level %s"
*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.

please need help to understand this Error.
Tom Kyte
December 28, 2010 - 10:21 am UTC

read the last sentence - it explains what to do. It is something that should not be possible to happen.

If you can post a SMALL example that causes that (you do not need 5,000 lines of code - you should not need more than 10 or 20 lines of code at most), we'll take a look at it here - otherwise - do what the last line of the error message quotes.

Getting error on login "ORA-06512: at "SYS.DBMS_SESSION", line 153"

New bee, January 06, 2011 - 7:05 am UTC

Hi All,

I am getting below error while trying to login to oracle db.


SQL> conn scott/tiger@ora26
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00922: missing or invalid option
ORA-06512: at "SYS.DBMS_SESSION", line 153
ORA-06512: at line 2

If anyone have faced the same earlier then help us to resolve it.

Thanks in advance.

regards,

Tom Kyte
January 06, 2011 - 8:38 am UTC

you have installed a logon trigger.

It has bad code in it

it is failing


therefore - anyone without DBA privileges is locked out


solution: log in as DBA and disable the trigger and then fix it.

Getting ORA-00942 while dropping any user

A reader, June 27, 2011 - 2:27 am UTC

Hi Tom,

I am getting the below error while dropping any user in my database:
SQL> drop user sysman cascade;
drop user sysman cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 12


Please advise.
Tom Kyte
June 27, 2011 - 11:20 am UTC

do you have a DDL trigger in place? query the dictionary to look for drop related triggers.

I think you do, you will find it, it has dynamic sql in it, it is trying to access a table it cannot.

ORA-00604 - on 11.2.0.2

Rajeshwaran Jeyabal, December 18, 2011 - 8:29 pm UTC

Tom:

I was connecting to 11.2.0.2 database from sql*plus window and getting this error. Can you explain on this please?

idle> connect app_user/app_user@app_dev
ERROR:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-30032: the suspended (resumable) statement has timed out
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'


idle>

Tom Kyte
December 19, 2011 - 7:26 am UTC

you are unable to extend an undo segment - hence we cannot perform a given transaction - and you have asked us to perform one implicitly by having auditing on.

get in touch with your DBA - they'll be the ones to correct it.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

neha, February 18, 2012 - 2:56 am UTC

when I am re executing a view it gives me error of ORA-06502: PL/SQL: numeric or value error: character string buffer too small. This view has 142 columns and about 13 tables connected with it.
When I create a view with only 28 columns it creates but when I add one more on that it gives such error.
And this view is already exists so when I tried to count then error comes:
ora-00600 internal error code,arguments:[qctopn1], [],[],[]
my alert log showing this error.
What should I do to remove this from my database.
Tom Kyte
February 19, 2012 - 5:54 pm UTC

please utilize support for an ora-600.

ORA-00604: During FBDA process in 11.2.0.2

Rajeshwaran, Jeyabal, July 20, 2012 - 7:44 am UTC

Tom:

I have enabled the Oracle flashback data archive.
Whenever i enable FBDA i am getting the below error. when i disable this feature the error is not coming.

I will try to provide you a complete testcase (as small as possible) to replicate it, Do you aware of any open bug with this FBDA feature in 11.2.0.2 causing this error?

undo_management = Auto
undo_tablespace = UNDOTBS1
undo_retention = 900
UndoTablespace (UNDOTBS1) SIZE =2GB

Databse: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
OS: Linux
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
00604. 00000 -  "error occurred at recursive SQL level %s"
*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.


Tom Kyte
July 30, 2012 - 7:56 am UTC

so, increase the size of your undo tablespace.

It is basically saying "hey, I need this undo to be processed for the flashback data archive, so you cannot have it yet - and you'll need to extend undo" but then discovering that it cannot extend undo.


your undo tablespace is just too small.

ORA-00604 following other errors

peter, September 27, 2012 - 1:31 am UTC

Hi Tom,
Our EJB throws the below errors:
at $Proxy59.insertBusinessLog_SMS(Unknown Source)
at business.db.DBFacade.insertBusiLog_SMS(DBFacade.java:879)
... 3 more
Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STANDARD"
ORA-06512: at "AIRBH.AIRBH_INSERT_BUSILOG_SMS", line 26
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-06512: at line 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:212)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:951)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:289)at com.watchdata.airbh.system.dao.AirbhBusinessLogGprsEJBBean.insertBusinessLog_SMS(AirbhBusinessLogGprsEJBBean.java:406)
at sun.reflect.GeneratedMethodAccessor107.invoke(Unknown Source)atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
And the procedure:
create or replace procedure AIRBH_Insert_BusiLog_SMS(v_businessLogID IN BUSINESS_LOG_GPRS.ID%type, v_agentPhone IN VARCHAR2,v_cmdType IN CHAR,v_itemName IN VARCHAR2,...
BEGIN Insert Into BUSINESS_LOG_GPRS ( ID, ..,COMMAND_TYPE,.., MO_TIME, State,CONN_TYPE )Values ( v_businessLogID, v_age...) end...Can y give some advice thx

Long time no new comment

A reader, November 08, 2012 - 9:24 pm UTC


similar error

Rakesh, June 26, 2013 - 5:58 pm UTC

Tom,
Need you expert help here...I am issuing a simple select against a table
Our version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi


select * from order_fct where order_Date = 20120101

I am getting the below error:
ORA-00604: error occurred at recursive SQL level 1
ORA-01405: fetched column value is NULL

order_fct is partitioned and has only the max partition.
If i run the below sql, it runs fine

select * from order_fct partition(ORDER_FCT_MAX) where order_Date = 20120101

Any leads?



Tom Kyte
July 01, 2013 - 7:13 pm UTC

turn on sql tracing, i see it is recursive sql - could be an auditing routine you have in place.

look at the trace file that is generated, see what sql is being run on your behalf, that might trigger a rememberance of something you put into place...

Doh

A reader, July 01, 2013 - 7:46 pm UTC

where a date is a number...a DATE isn't a NUMBER.
Here we go again.
It is truly amazing that this implicit 'trust' still exists.

I never got a DATE without my NUMBER, but I know that FORMATTING and/or EXPLICITLY CONVERTING really MATTERS, if you want to get lucky on a DATE ;)

Disclaimer: just spent a working day on debugging that same old procedural slow-by-slow code.
Good ol' 4181 again, and lots of WHEN OTHERS...
*end of blowing off steam*

Number & Date

Max, July 28, 2014 - 10:15 am UTC

Maybe he defined the "order_date" as Number(8). having it always stored as to_number(yyyymmdd) is more often used as you might think. even as indexes are faster on 8byte int rather than full date columns. sorting/partitioning might be supported more easily. so without full table-declaration and explaination of the code behind do not throw it to hell at once...

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library