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

Linked In?

I’m not sure how many people are aware of Linked In, but it seems to be a pretty cool idea.

Basically it allows you to connect with other people around the world, to share common interests, but also to build a network of colleagues. Almost like a work-related Friends Re-united. It seems to be aimed more at the American audience than elsewhere, but there is a job search facility on the site as well, plus a certain degree of community. I’ve just joined the Club Ex-Oracle community, which seems to be growing…

Registration is free, and then you can contact friends or colleagues either through the system (if they’ve registered), or email them a link to join your network. It seems like a great contractor tool for keeping in touch – if you change jobs regularly, then this could be the tool for you, since it allows you to email your network with news. Finally, you can create an on-line CV (so what’s new there?), but ask people that are in your network to comment on your performance. My on-line profile can be seen here.


Well, finally I thought I’d create myself a blog. Hopefully I won’t need to rename it – it could potentially get embarrassing to reveal on such a public scale that I don’t really have a lot of thoughts.

So, what am I going to put in here? Good question!

Originally I thought about having a Wiki to store some useful code and hints and tips, but there are already lots of Oracle Wiki sites out there (check out the one at Consultium here which has some good all-round tips).

So I thought that it might be easier to create a Blog, so that I can just drop stuff here and (if you want to!), you can comment on it. The main content will be Workflow related hints, tips and how-to’s – if anyone has anything to add here, then please contact me for details on how to post stuff here. If you want to comment on anything, then you’ll need to register, but like everything on the site (so far, at least!), it’s free to do so. Hopefully this section, plus the forum will provide lots of interesting Workflow development tips…

Apart from the Workflow stuff, I’ll probably add some other posts here of a non-Oracle nature – just general musings and things that I think people might find interesting.

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.