I recently hit an intermittent error when launching a Workflow process from an event subscription, which took me a while to resolve. Whilst passing a series of event parameters (in the standard WF_PARAMETER_LIST_T ADT), every so often, one workflow would not start. Of the twenty that I tried to start, one always failed – at least it was consistent, but it wasn’t immediately clear why this particular record failed to raise the event correctly.
Firstly, there was no sign of anything happening at all for the record, which took me a while to get to the bottom of. Eventually, I realized that there was no listener running for the WF_ERROR agent. I should have found it earlier, but it slipped my mind completely that the listener may not be running – eventually I looked at the number of records on the queue and found that there were at least 40 messages waiting to be processed. (Aside – the reason that the listener was not scheduled correctly will be in a future post).
When I ran the agent listener, there were a string of messages which identified what the problem was – part of the error stack reported:
So there’s the start point to the problem – when setting the ORIGINAL_HIRE_DATE item attribute to ’21-NOV-1983′, the code failed. The attribute was setup as a date type, with no format mask, so there is obviously a problem converting between the date formats.
The value of the parameter was being derived from a DATE field in the database, converted to a VARCHAR2 (to become an event parameter, which only accepts text values), and then converted back to a date to be stored as an item attribute – no wonder there were issues in the conversion process.
Delving into the code for the SetEventItemAttr procedure shows how the engine does the translation from text to date. If the date attribute definition has a format mask, then this mask is used for the conversion from text to date. If the item attribute does not have a date format mask, then the value that is used is the constant WF_CORE.CANONICAL_DATE_MASK – ‘YYYY/MM/DD HH24:MI:SS’. If you try to convert a date from the DD-MON-YYYY format into this format, any date from the year 2000 onwards works (in that there is no error produced! The date converted is still incorrect, but there is no error). Dates earlier than the year 2000 will result in an error being raised – exactly what I was seeing in my test cases.
So – how do you get round the problem? There are two ways that you can do this. Firstly, change the code that builds the parameter list, so that the dates are passed to the event parameter list in the Workflow Canonical date format. Alternatively, put a format mast on the attribute in the Workflow. This is the mask that will be used instead of the value from WF_CORE, but make sure that it matched the format mask that is being used in the code which builds the parameter list. I’ve adopted the second of these approaches, but that’s purely my preference – indeed you could make changes to both the Workflow item attribute definition and the PL/SQL. As long as the date format masks match, that is the important thing – without them matching, either the dates will not be passed correctly, or the conversion will error.
What would have been nice would be for the WF_ENGINE conversion code to accept pretty much any date format and then store that in the database correctly. I saw code by Steven Feuerstein in 1998 that did this, so it would be a nice to have. At least I (and you) know the reason why the date conversion errored and what to do about it.