java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got NCLOB in RSA Identity Governance & Lifecycle

[#1]

Article Number

000037124

Applies To

RSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 7.0.2 P11, 7.1.0 P05 and up

Issue

The public schema view CHANGE_REQUEST_VARIABLE (accessed as ACMDB.CHANGE_REQUEST_VARIABLE or AVUSER.PV_CHANGE_REQUEST_VARIABLE) column VARIABLE_VALUE had a datatype change in order to solve a length truncation issue. This change may require modifications to custom queries using that column in that view. As of versions 7.0.2 P11 and 7.1.0 P05 the column is returned as a datatype CLOB instead of a VARCHAR2.

In the WorkPoint.log and wpqmonitor.log you will see errors similar to:

2019-01-16 10:52:23,913 [Worker_actionq#Script Queue - Normal#WPDS_99] ERROR com.workpoint.server.script.ScriptEngine - A script error has occurred. Error occurred in Statement #1 in Script ID 99:WPDS, Script Name = 'SQL Query', Script type = 'Action'. Job ID = 99999:WPDS, Process Ref = 'WF_AP_99', Node Name = 'MyNodeName', WorkItem = 9999999:WPDS:1
java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got NCLOB
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
 at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
 at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901)
 at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
 at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
 at com.aveksa.server.workflow.scripts.action.QueryActions.processQuery(QueryActions.java:447)
 at com.aveksa.server.workflow.scripts.action.QueryActions.access$000(QueryActions.java:49)
 at com.aveksa.server.workflow.scripts.action.QueryActions$1.preProcess(QueryActions.java:420)
 at com.aveksa.server.workflow.RetryEnabledOperationsJobUtils.executeJobStrategyWithProcessing(RetryEnabledOperationsJobUtils.java:216)
 at  com.aveksa.server.workflow.RetryEnabledOperationsJobUtils.saveWithProcessing(RetryEnabledOperationsJobUtils.java:112) at com.aveksa.server.workflow.scripts.action.QueryActions.query(QueryActions.java:415)
 at sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:606)
 at com.workpoint.server.script.StatementEngineJava.execute(Unknown Source)
 at com.workpoint.server.script.ScriptEngine.A(Unknown Source)
 at com.workpoint.server.script.ScriptEngine.execute(Unknown Source)
 at com.workpoint.server.monitor.ActionMonitorHelper.A(Unknown Source)
 at com.workpoint.server.monitor.ActionMonitorHelper.execute(Unknown Source)
 at com.workpoint.services.impl.ScriptExecAsyncServiceImpl.executeScriptMonitor(Unknown Source)
 at com.workpoint.client.Monitor.executeScriptMonitor(Unknown Source)
 at com.workpoint.queue.work.ActionQWorker.A(Unknown Source)
 at com.workpoint.queue.work.ActionQWorker.run(Unknown Source)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1152)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:622)
 at java.lang.Thread.run(Thread.java:748)  

Cause

This was a necessary change to resolve a column truncation issue in defect ACM-85409. Added safeguards to prevent error when dumping a CLOB into a VARCHAR2 which has a maximum length of 4000 bytes.

Resolution

Some SQL SELECT queries going against view CHANGE_REQUEST_VARIABLE may need to be modified if they return column VARIABLE_VALUE.  Column function TO_CHAR() or similar changes many need to be used to prevent the datatype mismatch error.

The following query may help identify SQL Nodes using this view:
 

SELECT p.name AS Workflow, pn.name AS Node, ud.var_cvalue AS SQL_SEL
FROM wp_user_data ud
INNER JOIN wp_proc p ON p.proc_id = ud.proc_id
INNER JOIN wp_proc_node pn ON pn.proc_id = p.proc_id AND ud.data_id = pn.proc_node_id
WHERE ud.var_name = 'SqlQuery'
AND UPPER(ud.var_cvalue) LIKE '%CHANGE_REQUEST_VARIABLE%';

 

Do not change the value "SqlQuery". Leave the query as it is.