0

I have a table SyncTokenLock that has column lockName that is of CLOB type. When I run following query from SQLDeveloper -

select * from SyncTokenLock where 
lockName='com.vmware.horizon.datastore.impl.ProvisioningStateDataServiceImpl';

I get following exception -

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

I get similar error when this query is executed through Hibernate (3.6.10) against Oracle 11g. Hibernate throws following exception -

ORA-00932: inconsistent datatypes: expected - got CLOB

Any idea what could the reason be.

devang
  • 5,376
  • 7
  • 34
  • 49
  • It seems the error is because Oracle does not allow `CLOB` datatype in where clause. Not sure. Could anyone confirm. – devang Aug 01 '12 at 19:51

2 Answers2

0

Correct, you can't use equality with a CLOB in the WHERE clause. But you can do this:

SELECT * FROM SyncTokenLock 
 WHERE dbms_lob.substr(lockName, 100) = 
           'com.vmware.horizon.datastore.impl.ProvisioningStateDataServiceImpl';

Does your column really need to be a CLOB? Are you expecting values over 4000 characters? If not, use a VARCHAR2.

eaolson
  • 14,717
  • 7
  • 43
  • 58
  • Even I am not sure if I need it to be CLOB. I have legacy code that runs on PostgreSQL. I need to add support of Oracle. This column was specified as VARCHAR(4096), so used CLOB when designing schema for Oracle. As of now I changed the column type to VARCHAR2(4000) from CLOB to make it work. Not sure how foolproof is that. Your solution would work against Oracle but not against PostgreSQL. I am using hibernate to do the database talking for Oracle and PostgreSQL. – devang Aug 02 '12 at 05:21
0

Instead of using the equal sign, you may use like:

select * from SyncTokenLock where lockName like 'com.vmware.horizon.datastore.impl.ProvisioningStateDataServiceImpl';
Juan Serrats
  • 1,358
  • 5
  • 24
  • 30
Baran Bekçi
  • 1
  • 1