What Situations Cause Oracle Packages to Become In

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

BTW, If I'm completely wrong about the situation... apologies in advance

Caught by surprise?

Not sure what the implications of that are...

Did something break in production?

What EXACTLY happened?

The reason I ask is because understanding every possible change's ramifications is much harder than dealing with the outcome. Why the invalidation become an issue? My guess is because you got an "Existing state of Package has been discarded" error in your application. Is that the REAL issue?

Again I suspect that it is and if so, let's just deal with that instead of the list of changes which as I put in a comment is version specific. (11g tracks dependency down to the column of a table instead of the table as a whole for example).

This may not seem like an important error to you if you're not using package state. If you were this would be an important error and you wouldn't have been surprised, so I'm guessing you're not.

Since you are not this error is ok to ignore. Since you can safely ignore it, you can code your client app to ignore this error and retry your call, because, as others have pointed out Oracle will recompile your package for you. This is a worthwhile exercise. Because rather than knowing every possible thing you need to worry about when you make a change, and then in the emergency fix you forget one of those, your app will just handle it and move on, without worry.

Answer 2

I agree with Thomas Jones-Low however there are a couple more issues to do with long sessions and recompilation.

If you reference a package in a session and that package (or a dependant package) gets recompiled during the same session then you'll get oracle error "ORA-06508: PL/SQL: could not find program unit being called"

Once you've referenced the package in a session you generally can't change the package without invalidating it for that session. This is a particular problem for development environments where packages change frequently but also a problem for production environments where you want to do a small patch without taking the whole environment down. Note that this error will occur even when there are no errors in the changed packages.

Answer 3

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.

Answer 4

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 5

If try to execute an invalid Oracle package, Oracle will try to compile it. Only when it remains invalid after compiling Oracle will throw an exception.

Answer 6

In addition to Thomas Jones-Low's answer, if you only modify the package BODY, a dependent object might not be marked as invalid.

However, as soon as you modify the package specification, that is bound to happen.