dropping procedure (circular dependency problem)

[reydp]

hello,
I have this one problem now in a test server. where in a developer wasn't able to drop procedures. As I trace the codes, the procedure was calling another procedure. And the procedure that was called also calling the procedure that intended first to be drop. Neither of this two procedures, can be dropped. Which means this scenario is forming what is called a circular dependency problem.

Thus anybody here experience the same problem? I intend to raise TAR this problem to Oracle Technical Support, but was thinking first if some you guys here do have experience.

v8.0.5/Solaris 7

[reydp]

forgot to include the error mess:
ORA-00600: internal error code, arguments: [4882], [36419068], [47566956], [35647452].

I try to revise the code by just putting NULL; between BEGIN and END,
but still it produced the same error.

[akhadar]

check the following link
This is a bug
http://metalink.oracle.com/metalink/...p_id=89733.999

[reydp]

Thanks Akhadar,

I did surf in Metalink, but i didn't notice that it was a bug related problem until your post. Most of the recommendation is to upgrade or open a tar.

I'm working on just removing the object from dependency$. If it works, then no need for me to upgrade.

Anyway, many thanks

[reydp]

it's ok now. I was able to remove the procedures.
here is the sqls executed for those who are curious:
SQL> select object_id from dba_objects
2 where object_name = 'CREATE_OVERSHORT';

OBJECT_ID
----------
66763

SQL> select * from dependency$
2 where d_obj# = 66763;

D_OBJ# D_TIMESTAMP ORDER# P_OBJ# P_TIMESTAMP D_OWNER# PROPERTY
---------- ----------- ---------- ---------- ----------- ---------- ----------
66763 27-AUG-2004 0 309 19-NOV-1996 79 1
66763 27-AUG-2004 1 57574 23-AUG-2004 79 1
66763 27-AUG-2004 2 57576 27-AUG-2004 79 1
66763 27-AUG-2004 3 65927 21-AUG-2004 79 1

SQL> l
1 select object_id from dba_objects
2* where object_name = 'CREATE_SL_DTL_PRC' and object_type = 'PROCEDURE' and o
wner = 'DAHLIA'
SQL> /

OBJECT_ID
----------
57576

SQL> select * from dependency$
2 where d_obj# = 57576;

D_OBJ# D_TIMESTAMP ORDER# P_OBJ# P_TIMESTAMP D_OWNER# PROPERTY
---------- ----------- ---------- ---------- ----------- ---------- ----------
57576 27-AUG-2004 0 309 19-NOV-1996 79 1
57576 27-AUG-2004 1 59623 22-NOV-2002 79 1
57576 27-AUG-2004 2 57571 02-OCT-2002 79 1
57576 27-AUG-2004 3 57394 28-OCT-2003 79 1
57576 27-AUG-2004 4 57570 27-JAN-2004 79 1
57576 27-AUG-2004 5 172 01-OCT-1999 79 1
57576 27-AUG-2004 6 66149 05-JUL-2004 79 1
57576 27-AUG-2004 7 65968 17-AUG-2004 79 1
57576 27-AUG-2004 8 66063 04-AUG-2004 79 1
57576 27-AUG-2004 9 66070 26-JUN-2004 79 1
57576 27-AUG-2004 10 66754 25-AUG-2004 79 1

D_OBJ# D_TIMESTAMP ORDER# P_OBJ# P_TIMESTAMP D_OWNER# PROPERTY
---------- ----------- ---------- ---------- ----------- ---------- ----------
57576 27-AUG-2004 11 66763 27-AUG-2004 79 1

12 rows selected.

SQL> create table bak_dependency$
2 as select * from dependency$
3 where d_obj# = 66763;

Table created.

SQL> select * from bak_dependency$;

D_OBJ# D_TIMESTAMP ORDER# P_OBJ# P_TIMESTAMP D_OWNER# PROPERTY
---------- ----------- ---------- ---------- ----------- ---------- ----------
66763 27-AUG-2004 0 309 19-NOV-1996 79 1
66763 27-AUG-2004 1 57574 23-AUG-2004 79 1
66763 27-AUG-2004 2 57576 27-AUG-2004 79 1
66763 27-AUG-2004 3 65927 21-AUG-2004 79 1

SQL> delete from dependency$
2 where d_obj# = 66763;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> drop procedure dahlia.create_overshort;

Procedure dropped.

SQL> drop procedure dahlia.create_sl_dtl_prc;

Procedure dropped.

SQL> DROP TABLE BAK_DEPENDENCY$;

Table dropped.