Raising Date Event Parameters

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:

Date error message

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.

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.