DBAdapter – java.sql.SQLException: ORA-00932: inconsistent datatypes: expected – got CLOB
Observed on BPEL PM 10.1.3.3.
We had a set of master-detail tables that had one of the columns as a CLOB, and a process that is polling for new or changed records on these tables.
When the process is deployed, the endpoint activation fails complaining “Expected – CLOB”.
The reason is that when the toplink query is generated, it generates a DISTINCT clause in the select statement, and DISTINCT clause cannot be applied if one of the return columns are CLOBs.
The solution – Open up the toplink mappings XML file and update the batch-attribute reading value to “false” from “true”.
We figured this out from the Troubleshooting and Workarounds section of the Adapters documentation.
I am inlining the relvant snippet from the document for convenience.
A SELECT returning CLOB values must not use the DISTINCT clause. The simplest way to avoid DISTINCT is to disable batch attribute reading from A to B. Batch reading is a performance enhancement that attempts to simultaneously read all Bs of all previously queried As. This query uses a DISTINCT clause. Use joined reading instead, or neither joined reading nor batch attribute reading.
Because both DISTINCT and CLOBs are common, you may see this problem in other scenarios. For example, an expression like the following uses a DISTINCT clause:
SELECT DISTINCT dept.* from Department dept, Employee emp WHERE ((dept.ID =
emp.DEPTNO) and (emp.name = ‘Bob Smith’));