Oracle 9i : Compiling 1 package ruins all dependencies!!

[adcworks]

Hi All,

I have been having a nightmare with an ORA-06508 (see http://www.dbasupport.com/forums/sho...threadid=31684)

I have managed to find something which replicates the error on tap now. What is happening is that when I compile a custom package on the fly, all its dependencies get broken.

When I say broken I mean select object_name, status from user_objects where status='INVALID' yields results.

I did the same query for dba_objects and find 1,200+ INVALIDs. This cannot be right.

I successfully ran catproc.sql last night..would this have caused problems?

I always used to compile packages on the fly in Oracle 8 but it never started chucking ORA-06508s.

Enlightenment required and appreciated!

[TimHall]

Hi.

You should always run the ORACLE_HOME/rdbms/admin/utlrp.sql script after running the catproc script. This will recompile all invalid objects. Once this is done you can see how many objects are actually invalidated when you compile your package.

Compiling a package specification will invalidate any package specifications and bodies that reference it. Remember, if you only need to compile the body, then don't compile the specification. Use:

ALTER PACKAGE my_package COMPILE BODY;

rather than:

ALTER PACKAGE my_package COMPILE;

Cheers

Tim...