Syntax causes are failure in GEL scripts starting in 16.0.3.
Although the query works fine in the backend, it when executed through the JDBC driver.
The query parsing is different.
Examples of SQL that fails:
Although the query works fine in the backend, they will fail when running in GEL script/process, as the query parsing is interpreted differently by the driver.
|Date comparisons fail with to_char function|| ORA-01861: literal does not match format string
Resolution: Use to_date function
|When calling a stored procedure NIKU.Z_EXAMPLE_SP
||SQL error: org.apache.commons.jelly.JellyTagException: null:23:17: <sql:update> CALL NIKU.Z_EXAMPLE_SP: ORA-06576: not a valid function or procedure name
Resolution: Append empty parenthesis after stored procedure
|SUBSTR(TRIM(q'(On-Premise (North America))'), 1,2000 ) col11||Fails with the SQL92 error
/it will work if you remove one Parenthesis [ from )) to )] /
Release : 16.0.3
Starting in 16.0.3, Clarity utilizes new jar/drivers, which can change how SQL is parsed or interpreted,
therefore the best practices in SQL development will need to be adhered to.
GEL Scripts Error ORA-06576 After Upgrading