Oracle asks, “got CLOB?”

Posted: 04/22/2013

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,