Unable to execute sql code via PX

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Identity Suite

Issue/Introduction

When configuring a CA Identity Manager PX Policy, Policy Xpress Action crashes with an error: 

ERROR MESSAGE: Echec de l'exécution de la requête
; Failed to execute prepared statement: SELECT '30/09/2020 11:01:18' on data source: java:/iam/im/jdbc/jdbc/iam_custom. Exception: ORA-00923: FROM keyword not found where expected
; ORA-00923: FROM keyword not found where expected
; ORA-00923: FROM keyword not found where expected

or with this Error in UI:

The test failed to execute with the following error: An error occurred while executing a query that caused Failed to execute prepared statement: UPDATE IDP.MYTABLE SET ID='<number>', ROL='Data', LOGINID='userid',DATE_API=date', SUCCESS='false', STATE='test' WHERE CODEUID=code; on data source: java:jboss/datasources/jdbc/IDP-ds. Exception: ORA-00933: SQL command not properly ended ;ORA-00933: SQL command not properly ended ;ORA-00933: SQL command not properly ended .

The query runs OK against Oracle for iSQL

Environment

Release : 14.3 CP2, 14.4.2

Component : CA IDENTITY SUITE (VIRTUAL APPLIANCE)

Resolution

in iSQL the end of each query is closed with a semi-colon ";"  but CA Identity Manager (IM) does not need the character.  Omitting the character allows the query to execute as expected.

Also this may happening when putting the query with commas. i.e:

UPDATE IDP.MYTABLE SET ID='<number>', 
ROL='Data', LOGINID='userid',
DATE_API=date', SUCCESS='false', STATE='test' WHERE CODEUID=code;

After removing the commas and semi-colon worked as expected.

i.e.

UPDATE IDP.MYTABLE SET ID='<number>' ROL='Data', LOGINID='userid' DATE_API=date', SUCCESS='false', STATE='test' WHERE CODEUID=code