AnsweredAssumed Answered

IGL 7.0.2 : Workflow Editor throwing Oracle errors on SQL nodes with WF variables

Question asked by Andy Cheek on May 26, 2017
Latest reply on Aug 2, 2017 by Andy Cheek

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

Outcomes