Oracle: Error when executing query

Question

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.

Answer 1

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.

Answer 2

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

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