COURSE of the MONTH

oracle workflow: status rows for activities not getting created (wf_item_activity_statuses)

When I try to launch an Oracle custom workflow using the therefore meant PL/SQL functions (createProcess, startProcess)
I get the error message:
ORA-20002: 3101: Status row for item 'XPLA_ORD/ORD53579', activity ID '117141' does not exist.
ORA-06512: at "OWF_MGR.WF_ENGINE", line 3178

There has to be a problem inside the design time or runtime tables preventing the status rows from being created.
I have 2 custom workflow set up on the same database, and I have only one of them that throws this error.

I already tried cleaning up the workflow using scripts inside the workflow sql folder, and purging the workflow data using the WF_PURGE api.
I even went as far as removing all design and runtime data for this itemtype with the wfrmitt.sql script.
After this I uploaded the custom workflow again without any succes.

I have encountered this problem before on another environment, but after a month I was allowed to delete the workflow all together and reinstall everything from zero, resulting in a solution.
However I knew I couldn't do this forever on all environments, and I'd like to know a good solution before this problem occurs on our production environment.

Who is Participating?

After looking at this again, I didn't find a commit statement inside the timer package,
BUT: I found a stray commit in a previous package, so when the timer package errored the rollback couldn't proceed because of the commit which messed up the savepoint.

Thank you for your help.
Best regards,
Erwin.

0

erwin_huybregtsAuthor Commented: 2008-02-07

I've been trying to debug the PL/SQL packages from oracle itself.
And by coincidence I found out that if I place a commit; right after the insert statement for a wf_item_activity_statuses record, the workflow starts up without a problem.

However when I take the commit out again, the wf_engine.startprocess procedure fails every time.

Funny thing is that it only fails for a specific item type and not everything.
I have another flow on the same system with another item_type working fine.

There has to be something wrong in the design time tables. This would be the most logic explanation to me. Only thing is that I can not find anything that's wrong in there.

Any help would be very appreciated since last time I had this problem on the development environment I had been searching for months before I just deleted everything and reinstalled.

I am currently running on a 9i instance,
and I do not know the steps to reproduce this problem.

Could it be that corrupt data got into the workflow design tables due to a malfunctioning network connection?
There recently has been a power outage in the data center where the servers are hosted, and at that time I was working on the workflow using workflow builder.

Possible, but highly unlikely, since that kind of failure would malfunction consistently and not be dependent on any specific item type. Are you sure there have been no recent changes to the code that might have brought about this error? If yes, then raise an SR with Oracle on Meta-Link and seek their advise.

0

erwin_huybregtsAuthor Commented: 2008-02-07

I have only been analyzing the way the PL/SQL code is creating the item activity status rows.

The only code change I have made during these analyses is that I accidentally typed a commit; statement right after the insert of the item activity status row, resulting in a succes of the startProcess method.

However I removed the commit as soon as I noticed I had typed this into the wrong editor, resulting in a failure of the startProcess method again.

I'm launching a service request nonetheless as we speak, and I will post the solution or any other helpfull items as soon as I get them.
However I'm not suspecting much of the service request, because I had launched a similar one for the development environment a few months ago, resulting in no solution.
On the development environment I had the oppurtunity to delete the workflow alltogether to reinstall everything, but I can not do this on the test environment, and surely not on the production environment should this issue occur there.

1. As of now, the WF process works for 1 of the 2 item-types even after removing the COMMIT. Correct?
2. This problem started only after u entered the WF for that specific item-type and not before that. Correct?
3. If u enter another WF for the same item-type, does that work?
4. Can you share the relevant schema-names / table-names / procedures / APIs?

0

erwin_huybregtsAuthor Commented: 2008-02-08

Hi I tested out some stuff and with the commit I got the broken flow that will start up, but it crashes later on on some own PL/SQL code, that is currently being used fulltime in the production environment.
And without the commit There's are "new" flow only that will work.

We're currently integrating 2 business flow into one, and that new flow (in development phase, only a lite version runs on test) works like it should.
The old flow is currently being used from development to production.

If I got the timing right, the problem started after some updates I did to the "old" flow on the test environment using Oracle workflow builder. I noticed the problem only when I launched a first test.

I have traced the exception to occur inside the WF_ITEM_ACTIVITY_STATUS.CREATE_STATUS
However I am not sure where in the method the exception is thrown.
Is there a way I can find out which exception is thrown? or will I have to define all know exceptions to find out?

I will now try to startup another flow of the same itemType of the flow that won't work (XPLA_ORD)

0

erwin_huybregtsAuthor Commented: 2008-02-11

When I start a new flow using the same item type it doesn't appear to give me any problems.
When I bypass the START_TIMER activity the flow boots up nicely and performs the requested activities like it should.

However when I try to send an event using the wf_event package I now get an "Agent does not exist" message

Is there actually a library of errors for the worklow like there is with the ORA-xxxxx errors?

Thank you for your help untill now already.

ORA-20002: 3802: Agent does not exist.ORA-06512: at "OWF_MGR.WF_CORE", line 280ORA-06512: at "OWF_MGR.WF_EVENT", line 405ORA-06512: at "GMS.XPLAN_EVENT_PKG", line 277ORA-06512: at "GMS.XPLAN_EVENT_PKG", line 206ORA-06512: at line 1