GEL Scripts Error After Upgrading To 16.0.3+

Products

• Clarity PPM SaaS

Issue/Introduction

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 CALL NIKU.Z_EXAMPLE_SP()
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 )] /

 

 

Environment

Release : 16.0.3

Cause

 

 

Resolution

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.

Additional Information

https://knowledge.broadcom.com/external/article?articleId=263831
GEL Scripts Error ORA-06576 After Upgrading