What Situations Cause Oracle Packages to Become Invalid?

Question

The scenario that created this question:

We have a package that is a dependency of another package, sometimes making changes to the "parent" package causes the dependent package to become invalid, but sometimes it doesn't.

It has caught us by surprise before.

It would be very useful to simply understand what causes invalidation so I could predict/plan for it.

Answer 1

Changing anything object that a package relies upon (e.g. tables, views, triggers, other packages) will automatically mark the package as invalid. As tuinstoel notes above, Oracle is smart enough to recompile the package when it is first used.

If you are concerned about this, every time you make schema changes (e.g. tables, views, triggers, procedures), run a DBMS_UTILITY.compile_schema (or have your DBA do it). This will force compile all the packages and let you know where, or if, there are errors before you find them the hard way.

Answer 2

Or you can query the following table to see what dependencies you have

   select *
   from dba_dependencies
   where name = 'YOUR_PACKAGE'
   and referenced_owner = 'ANYUSER' --- Comment this out if you are looking for yourself
   and owner = USER --- Or can be set to any user

This will show all dependencies. For your objects query user_dependencies.