For valid custom package, workflow gives invalid error...

[Arun]

Hi All,

I have a problem in my custom workflow. I have created a custom package and this package procedures are being invoked from the custom workflow. It was working fine till two days back. I needed some changes to the package procedures. So modified the functions and recompiled from the unix box using sqlplus command. It got compiled without error. And after this I reloaded the workflow. But, when  I try to execute the workflow from e-Business suite, it is giving an error like 'Dropped or altered package... Package state invalid'. The workflow is triggered thru business events. I can see the package as valid if I open it in SQL navigator. I tried recompiling and reloading workflow several times... Strange thing is that sometimes, it is working fine and sometimes it gives the error as above.
One more strange thing... An errored out workflow, when I retried (from the node that erroed out, click on Retry button)  from the status monitor, it worked perfectly ! And the package invalid error went away... Any clue on this behaviour ?

Thanks in advance....

Arun

[Matthew Searle]

Hi Arun,

Sadly, this is expected behaviour.  That doesn't mean that you should be seeing the error, just that I'm not surprised that you have!!

When you recompile the package, everything is fine - your session can see that the package is there and is valid.  If you have another SQL session open at the same time as recompilation, if you try and execute the package in the second session you get the same error as you see in the Workflow.  Retry the same code, and the SQL session will pick up the new definition and run fine.

This is pretty much what the Workflow engine is doing - running in another session.  The first time it tries to run your code, it errors as it detects that the package has changed.  When you retry the activity from the notification, it works fine because it is picking up the new definition.  There are two questions that need answering - how do we get rid of the error? and why does the error keep happening?

The second question is slightly easier, so I'll address that one first.  I don't know.  Oracle don't seem to know.  When I worked for Oracle, I asked internally why this happened and no-one could tell me.  The trick seems to be to only recompile the package body rather than the spec as well, but that's not much of an answer.

So, how do you get rid of the error?  You need to force the session which is referencing the old version of the code to disconnect and reconnect, in order to pick up the latest version of the package.  The most effective (but least user-friendly!) is to bounce the database - this ensures that all the sessions are disconnected and will reconnect as necessary.  Next down the list, you can try bouncing Apache.  This should have the same effect, but hopefully is a bit quicker so less disruptive.  Finally, you can try bouncing all the concurrent managers Workflow Agent Listener Service and Workflow Mailer Service, which might refresh the session.

HTH,

Matt

[Arun]

Hi Matt,

Thanks a lot for that detailed description. After compilation, as you suggested, I restarted the listeners and it seems to work fine now. I manually restarted all the listeners by going to System Administrator > Oracle Applications Manager : Workflow Administrator  and then to Agent Listeners. Then clicked on each listener and stopped each one by one and then logegd out. Again, logged in, and same way started all listeners one by one. After that tried executing the workflow and it was working fine !

Is there any way to do this same thru concurrent program ? I have two conc pgms for WF. Workflow Background Process (every 5 mins) and OAM Applications Dashboard Collection (every 10 mins). When I stopped OAM Applications Dashboard Collection and scheduled it again, even then it seems like working....

Many thanks,

Arun