In the early days of migrating from IMG 6.9.1 to IGL 7.0.2 and have hit some errors in the WF editor that appear to be related to Oracle and syntax checking that is happening.
So far I have had the following errors when trying to edit SQL nodes …
{nodename} : ORA-01847: day of month must be between 1 and last day of month ORA-06512: at line 4
{nodename} : ORA-01403: no data found ORA-06512: at "{schema}.{storedprocedurename}", line 17 ORA-06512: at line 4
Both messages comes from SQL nodes that have been working quite happily under IMG 6 for months, yet the SQL includes WF variables. I’m guessing here, but I suspect that whatever is being invoked/used to do this extended Oracle checking is NOT taking account of the variables and treating the variable name as real data instead of a placeholder to be substituted with a real value at execution time. I get it – if you just had a string with the value ${jobUserData_TERMINATIONDATE} there is no way that would work in a TO_DATE(….,’DD-MON-YYYY’) function … but it is NOT the actual value, certainly not when just editing the WF definition.
Furthermore the second message comes from the second of those SQL nodes, one that is calling a custom stored procedures, again using WF variables to provide the input parameters. The message (and the explanations for it that I have found online) actually seems to imply that it has not only validated the call to the proc but also evaluated a SQL statement within it against the database and decided it is a problem … again (presumably) because it has taken the WF variable names as actual data values.
Example SQL:
select trunc(to_date(‘${jobUserData_TERMINATIONDATE}’,'DD-MON-YYYY')) - trunk(sysdate) as TERM_DATE_DIFF from DUAL
Confirmed a result of enhanced SQL checking within the WF editor. All queries involving WF variables need to be written in such a way to avoid errors if "executed" with the design-time substitution as opposed to run-time substitution.
A pain but it works.