Oracle asks, `got CLOB?`


If you found your way, you’ve run into this error:

ORA-00932: inconsistent datatypes: expected - got CLOB

A CLOB is a character large object field that capable of storing far more text than a varchar can offer. In my case, a database field changed from a varchar2 to a CLOB in order to support more text that needed to be stored (I found out it changed when the error started occurring).

A CLOB cannot be compared with equals, like, etc. and the query I was running had a DISTINCT in it which has to compare the contents of the rows to the next to determine if they are the same. If you try to use standard commands like substr on the CLOB you will also receive errors. However, there are some functions that exist for dealing with this datatype and the following is what I used to fix the error I received:

DBMS_LOB.SUBSTR

In particular, when I was laying out my select fields the call looked something like:

DBMS_LOB.SUBSTR(INTENT_TXT) AS INTENT_TXT,

Leave a comment

Please note that we won't show your email to others, or use it for sending unwanted emails. We will only use it to render your Gravatar image and to validate you as a real person.