GEL Scripts Error After Upgrading To 16.0.3+
search cancel

GEL Scripts Error After Upgrading To 16.0.3+

book

Article ID: 264038

calendar_today

Updated On:

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