Invalid package error for a valid package!
May 30th, 2008 MattOne of the most annoying “features” of Oracle Workflow is an inconsistent error message that fires occasionally that the package is invalid, yet if you check the state of the package it’s valid. The problem only arises if you have compiled a new package header as well as package body, and the Workflow has run that package before.
When the package is run the first time (with the old version of the package), the Workflow engine essentially obtains a “pin” to that particular version of the package in memory. When you recompile the package spec and body, this is fine and (assuming the package is valid!) it will compile with no errors. If you try to execute a call to the package in that SQL session, it will run fine because you are executing your code in a session which has only ever executed the current version of the package.
When the Workflow engine attempts to execute the procedure, the version that it has cached is no longer valid, so you get an Oracle error: Existing state of package has been discarded. If you have configured the error handling correctly for the activity, this will invoke the standard error workflow, which will send a notification to a system administrator. If they open the notification and retry the node that is in error, the system will use the new version of the package, since it is now in a new session. However, this will only fix the one node – if a new process attempts to call the procedure again, you will get the same error.
So, how do you avoid the error?
There are only three suggestions here, each as impractical as each other.
1 – Don’t use packages. Instead, use procedures for PL/SQL activities in Workflow. When you recompile the procedure, you shouldn’t have the problem that you get in packages, since it should force a re-compilation. I’ve never even tried it, since as far as I am concerned, just using procedures isn’t a sensible option. I can’t think of the last time I just delivered one procedure or function – they tend to be parts of a much larger solution (particularly with a Workflow), and it makes sense to store them together in a package.
2 – You could create a new package every time you need to change it. This ensures that with every re-compilation, the Workflow engine can never have seen the package before and cached an old version. However, if the change is relatively minor, the impact on your Workflow process could be huge, since every call to the package will have to be replaced with the new package name.
3 – You only recompile the package body and don’t compile the package specification. This is fine, as long as you don’t need to change the specification, but if you have just added a new activity in the process and now need to add the code, there is no way to do it in a package. Again, a pretty useless suggestion, but I’ve seen it made a number of times.
How to resolve the error?
Given the above, it’s pretty likely that you will hit this error – particularly if you are doing some “real” Workflow development. If you are writing custom PL/SQL code, then you will hit this issue at some stage, if you haven’t seen it before.
There are a number of suggestions as to what you should do to resolve the issue here. In eBS there are more suggestions than in Standalone Workflow, since there are more components that could be the cause of the problem.
Each suggestion will result in a certain amount of disruption to the end-users (and developers). Sadly, the suggestions that are more likely to resolve the problem will have the biggest impact on them.
1 – Bounce the database. This will ensure that there are no cached packages in memory, since the database will have disconnected all SQL sessions. However, on a large development project, this is difficult to do on an ad-hoc basis. This can be done in eBS and Standalone.
2 – Bounce the webserver. Again, this will disconnect SQL sessions, which should resolve the problem. The Workflow engine will reconnect and pick up the new version. This can be done in eBS and Standalone, but again the impact on users is high.
3 – Stop concurrent managers, and any Workflow jobs that are running (agent listeners, background engines and so forth) in eBS. In a large number of cases, this removes the problem since it essentially bounces Workflow only – any sessions that Workflow has open should be disconnected and when they are restarted the new package should be used instead of the old one. A lot of people have reported that this works successfully, but I’m not convinced that this is a definitive answer.
When I worked at Oracle, I asked the same question of the Workflow product development team, and didn’t get an answer. I didn’t even get any suggestions about why it might happen or how to resolve it – gut feeling says that there is nothing official on this, although I have seen the question asked a number of times on Metalink, on OTN and here on the WorkflowFAQ forum. From now on though, I’m just going to refer people here – if there is a definite solution, I’ll be updating the blog, but I’m not sure we’ll get further than the suggestions above. Maybe it’ll be fixed in that famous “next release”…
