> -----Original Message----- > From: KARR, DAVID (ATTCINW) > Sent: Tuesday, January 12, 2010 9:07 AM > To: [email protected] > Subject: RE: Getting CLOB value gets "inconsistent datatypes: expected > - got CLOB" from Oracle > > > -----Original Message----- > > From: KARR, DAVID (ATTCINW) > > Sent: Thursday, January 07, 2010 3:54 PM > > To: [email protected] > > Subject: Getting CLOB value gets "inconsistent datatypes: expected - > > got CLOB" from Oracle > > > > I just tried to map my first CLOB column. I defined the field this > > way: > > > > @Lob > > private String longDescription; > > > > The XML has this (I'm aware of the fact that I don't technically need > > both the annotation and the XML): > > > > <basic name="longDescription"> > > <column name="LONG_DESCRIPTION"/> > > <lob/> > > </basic> > > > > When OpenJPA runs the query, I get back the following from Oracle: > > > > Caused By: org.apache.openjpa.lib.jdbc.ReportingSQLException: > > ORA-00932: inconsistent datatypes: expected - got CLOB > > I could still use some help with this. What I've found out so far from > my searches is that the resulting SQL uses "DISTINCT", which you can't > do when one of the columns being retrieved is a LOB (apparently). > > Here's the SQL I get (somewhat elided): > > SELECT DISTINCT t1.CATEGORY_ID, t2.CATEGORY_ID, t2.LONG_DESCRIPTION, > t3.CATEGORY_ID, t4.PRODUCT_ID, t4.PRODUCT_TYPE FROM CAT_CHLDCAT t0, > CATEGORY t1, DCS_CATEGORY_ES t2, CAT_CHLDPRD t3, PRODUCT t4 WHERE > t0.CATEGORY_ID = 'cat80059' AND t0.CHILD_CAT_ID = t1.CATEGORY_ID AND > t1.CATEGORY_ID = t2.CATEGORY_ID(+) AND t1.CATEGORY_ID = > t3.CATEGORY_ID(+) AND t3.CHILD_PRD_ID = t4.PRODUCT_ID(+) ORDER BY > t3.CATEGORY_ID ASC > > When I run this in my SQL browser, I get: > > Error: ORA-00932: inconsistent datatypes: expected - got CLOB > > SQLState: 42000 > ErrorCode: 932
For a little more background, apparently an ER was filed for Oracle a long time ago concerning this (but never implemented). If you have access to Metalink, the relevant information is "Bug 6646443: ALLOW BATCH READING OF LOB COLUMNS IN ONETOMANY AND MANYTOMANY RELATIONSHIPS".
