Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Peculiar problem.

Peculiar problem.

From: Manivannan.M <manivannan.m_at_tatainfotech.com>
Date: Fri, 22 Dec 2000 15:00:50 +0530 (IST)
Message-Id: <10718.125201@fatcity.com>


Hi list

Got a peculiar problem. While trying to drop an user I got an error,

ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded

After a lot of hue and cry I found out that its because of two procedures lying in the database.
Finally we found out which is causing the problem. There are two
procedures in the database and I'm giving here their contents.

Procedure 1:

create or replace procedure proc1 as
begin
proc2;
dbms_output.put_line('I am in Proc1');
end;

Procedure 2:

create or replace procedure proc2 as
begin
proc1_syn; ( proc1_syn is the synonym of procedure proc1). dbms_output.put_line('I am in Proc1');
end;

Both of them are invalid and rightly so. When you compile them you'll get a deadlock error. But more importantly, I can't either delete these procedures or edit and recompile them.
When try dropping the synonym of the procedure proc1 it gave me the following error,

ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded.

When I tried compiling I got this error

ORA-04045: errors during recompilation/revalidation of TBMS.PROC2 ORA-04020: deadlock detected while trying to lock object TBMS.PROC1

When I tried dropping the procedures it gave me the following error,

ORA-00600: internal error code, arguments: [4882], [9936752], [20285216], [9775880], [], [], [], []

Could anybody please tell me why this is happening and how to drop these two procedures...

Thanks in advance..

                                        Manivannan Muthukrishnan.

-----------------------------------------------------------------------------
    ADDRESS:                             E-MAIL:
    68/4, Site no:3,                     manivannan.m_at_tatainfotech.com
    80 Feet road,                        
    (opp) Deccan Studio,
    Indiranagar,                         PHONE:
    Bangalore - 560038.                  5284681  Ext: 464
------------------------------------------------------------------------------ 
Received on Fri Dec 22 2000 - 03:30:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US