Re: Cannot drop function

<pbeatty_at_my-deja.com> wrote in message news:7t23lt$n0h$1_at_nnrp1.deja.com...
> I have a function(and a view) in Oracle 8.0.5 that
> I cannot drop. The function calls a view which
> calls the same function. Both are invalid.
> I have also tried to replace both with a new
> function/view of the same name.
> Trying to drop either gives ORA-00600.
> Is there a way to get get these out of the
> database?
>
> Thanks in advance.
> Paul
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Hi, Paul,

Look at the following script.
I create such a problem, and resolve it by deleting records in the table sys.dependency$.
You must find out the real object IDs in your database before you want to delete the records in the table sys.dependency$.

SQL> connect scott/tiger;
Connected.
SQL> create function test_f return number   2 as
  3 begin
  4 return 0;
  5 end;
  6 /

Function created.

SQL> create view test_v
  2 as
  3 select test_f as n from dual;

View created.

SQL> create or replace function test_f return number   2 as
  3 d number;
  4 begin
  5 select sum(n) into d from test_v;   6 return d;
  7 end;
  8 /

Function created.

SQL> select * from test_v;
select * from test_v

       *
ERROR at line 1:
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SCOTT.TEST_F (referenced by SCOTT.TEST_V) SQL> select test_f from dual;
select test_f from dual

       *
ERROR at line 1:
ORA-06540: PL/SQL: compilation error
ORA-06553: PLS-907: cannot load library unit SCOTT.TEST_F (referenced by SCOTT.TEST_V) SQL> drop function test_f;
drop function test_f
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [4882], [45746836], [53119764], [45619016], [], [], [], []

SQL> connect sys/change_on_install
Connected.
SQL> select object_id from dba_objects

  2    where owner='SCOTT'
  3      and object_name='TEST_F'
  4      and object_type='FUNCTION';

OBJECT_ID

     2870

SQL> select object_id from dba_objects

  2    where owner='SCOTT'
  3      and object_name='TEST_V'
  4      and object_type='VIEW';

OBJECT_ID

     2869

SQL> delete from sys.dependency$
  2 where d_obj#=2870 and p_obj#=2869;

1 row deleted.

SQL> commit;

Commit complete.

SQL> drop function scott.test_f;

Function dropped.

SQL> drop view scott.test_v;

View dropped. Received on Sat Oct 02 1999 - 12:04:16 CDT