How to Resolve ORA-00932: inconsistent datatypes: expected - got CLOB

ORA-00932 for CLOB

We tried to DISTINCT a column typed as CLOB (Character Large Object), but it failed with ORA-00932.

DISTINCT

SQL> select distinct sql_fulltext from v$sql where sql_id = '8bfgn7n4ff82x';
select distinct sql_fulltext from v$sql where sql_id = '8bfgn7n4ff82x'
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

The statement in which, DISTINCT is to keep the uniqueness in returned values, it's very similar with UNION set operator.

UNION

Same error is thrown when it UNION multiple CLOB values.

SQL> select sql_fulltext from v$sql where sql_id = '8bfgn7n4ff82x'
  2  union
  3  select sql_fulltext from v$sql where sql_id = '2z0udr4sc402m';
select sql_fulltext from v$sql where sql_id = '8bfgn7n4ff82x'
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

ORA-00932 means that CLOB or BLOB cannot involve in such uniqueness selection, either with DISTINCT or UNION.

Solutions

To solve it, we should convert CLOB into normal strings (VARCHAR2) before returning unique values.

CAST Function

Here we use CAST function to convert it.

SQL> select distinct cast(sql_fulltext as varchar2(1000)) sql_fulltext from v$sql where sql_id = '8bfgn7n4ff82x';

SQL_FULLTEXT
...

In this case, we returned only the first 1000 characters if the result is distinguishable. It may be a drawback in your case, if there're many similar statements.

UNION ALL

We can remove UNION and if potential duplication is acceptable, or use UNION ALL instead of UNION operator if uniqueness can always be sustained.

SQL> select sql_fulltext from v$sql where sql_id = '8bfgn7n4ff82x'
  2  union all
  3  select sql_fulltext from v$sql where sql_id = '2z0udr4sc402m';

SQL_FULLTEXT
...

Comparison Operators

The bottom line is that LOB (large objects), either CLOB or BLOB cannot be compared in any way.

Therefore, comparison operators in Oracle cannot be used on LOB (large objects), for example:

• =, >=, <=, <> or !=
• IN
• BETWEEN
• DISTINCT
• UNION, INTERSECT, MINUS
• GROUP BY

That's why we can't create unique index on CLOB column without any modification. Error ORA-02327 will be shown.

Please note that, LIKE can be used on CLOB in WHERE clause.