Invalid package error for a valid package!

One 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”…

4 thoughts on “Invalid package error for a valid package!

  1. I’ve noticed some behaviour that may add to this topic.

    Package header compilation does cause the Invalid Package error (reproducible).

    Package body compilation sometimes causes the same error, but clearing all caches and logging out of eBusiness framework clears the error, even allowing successful retry of the function that is in error in Workflow Admin.

    All errors disappear magically overnight! This is definitely reproducible in our system and allows workflows that have triggered the error to proceed in Workflow Admin. This might be something specific to our implementation (ie backup / bounce routines) but I suspect that one of the overnight Oracle maintenance requests is doing it. The test system I’m using is on application version 10.5.10.2, DB version 10.2.0.2

    If this works for others then I would strongly recommend that users roll-out package changes after close of business.

    I hope this helps,

    Graham Hotchkiss

  2. Hi, Thanks for your post… I have faced this problem while developing a custom workflow for a client and since I was new to Workflow then, I dint know the cause of it….It took me quite some time to realise the cause behind it ….

  3. I can’t vouch for this being a perfect solution but it has helped me during development. Try the following query to see which sessions are ‘accessing’ your PLSQL package, then the following statement to kill them.

    SELECT * FROM v$session s WHERE sid IN (SELECT sid FROM gv$access WHERE object LIKE ‘YOUR_PKG_NAME_HERE’) ORDER BY sid;

    Then you can kill those sessions (at your own risk) using:

    ALTER SYSTEM KILL SESSION ‘&P_SID_COMMA_SERIAL’ IMMEDIATE;

    It’s rough, I know! Bouncing the ‘Agent Listener Service’ from the ‘Workflow Manager’ area of ‘Oracle Applications Manger (OAM)’ is still needed sometimes too but not always!

    Mark

Comments are closed.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close