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'));

Comments:

Hey i just visited your site for the first time and i really liked it, i bookmarked it and will be back :D

Posted by Arnold Archard on April 26, 2011 at 01:28 AM PDT #

So?

Posted by Precio Dolar en Cordoba on April 26, 2011 at 02:40 AM PDT #

I loved as much as you will receive carried out right here. The sketch is attractive, your authored subject matter stylish. nonetheless, you command get bought an shakiness over that you wish be delivering the following. unwell unquestionably come further formerly again since exactly the same nearly very often inside case you shield this hike.

Posted by Bastien on April 26, 2011 at 07:34 AM PDT #

The latest in LA

Posted by Christian Louboutin shoes on April 26, 2011 at 03:26 PM PDT #

Sunday lunch and we’ve been left a voucher for free meals at a local restaurant.

Posted by święty damian on April 27, 2011 at 07:39 PM PDT #

It’s hard to find knowledgeable people on this topic, but you sound like you know what you’re talking about! Thanks

Posted by buy baby clothes on April 29, 2011 at 04:14 AM PDT #

You'll get the same error if you use "GROUP BY clobcolumn" as well. Took me a long time to figure that out after one of our VARCHAR2 columns got changed to a CLOB.

Posted by Dodge on September 22, 2011 at 08:19 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Principal Product Manager

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today