Re: Peculiar problem.

Hi

I don't know why these happen, but try this.

Drop proc1_syn;
Drop proc2;
Drop proc1;

Or try delete reference to the other procedure from one of them, and compile both procedures.

Hope, this helps;

"Manivannan.M" wrote:
>
> 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
> ------------------------------------------------------------------------------
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Manivannan.M
> INET: manivannan.m_at_tatainfotech.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Dec 22 2000 - 05:07:53 CST