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.

Thanks in advance,

Erwin Huybregts
Database Administrator/J2EE developer
Xplanation Language Services NV
Technologielaan 21 B 2
3001 Haasrode

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

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

erwin_huybregtsAuthor Commented:
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.
Jinesh KamdarCommented:
Hi, I have access to a instance. Could you list down the steps for me to reproduce this ?
erwin_huybregtsAuthor Commented:
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.

Many thanks for your reply though.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jinesh KamdarCommented:
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.
erwin_huybregtsAuthor Commented:
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.

Thank you for your effort.
Jinesh KamdarCommented:
Questions : -

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?
erwin_huybregtsAuthor Commented:
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 don't know which names you need but here are some specifications:
old flow:
- item type: XPLA_ORD
- item key: ORD54837 (ORD + order id)
- root process: SETUPWF
- workflow table schema name: OWF_MGR
- custom table data model schema: GMS

new flow:
- item type: XTS_PRJ
- item key: PRJ54837 (PRJ + project id)
- root process: XTS_SETUP_WF
- workflow table schema name: OWF_MGR
- custom table data model schema: TSTREAM

the Oracle made PL/SQL methods used:
- WF_ENGINE.CREATEPROCESS(itemType, itemKey, rootProcess)
- methods to set item attributes (ex. wf_engine.setItemAttrNumber(attr, value))

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)
erwin_huybregtsAuthor Commented:
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 280
ORA-06512: at "OWF_MGR.WF_EVENT", line 405
ORA-06512: at "GMS.XPLAN_EVENT_PKG", line 277
ORA-06512: at "GMS.XPLAN_EVENT_PKG", line 206
ORA-06512: at line 1

Open in new window

Jinesh KamdarCommented:
Hey Erwin, try this link :
Apologies for redirecting u, but I'm swamped with some office issues right now.
Will try to research more as and when I get a chance.
erwin_huybregtsAuthor Commented:
Thank you for all your help, but we finally opted to bypass the code that was causing the problem to be able to continue our development/testing.

Kind regards,
erwin_huybregtsAuthor Commented:
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,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.