How to avoid “ORA-00932: inconsistent datatypes: expected – got CLOB” when distinct CLOB datatypes.

In Oracle, you can’t directly use “distinct” in queries on tables with CLOB types.

anbob@ANBOB>create table t(id int,c clob);
Table created.
anbob@ANBOB>insert into t values (1,'a');
1 row created.
anbob@ANBOB>select * from t;

                  ID C
-------------------- --------------
                   1 a
				   
anbob@ANBOB>select distinct id,c from t;
select distinct id,c from t
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

anbob@ANBOB>select distinct c from t;
select distinct c from t
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

anbob@ANBOB>create view v as select distinct id,c from t;
create view v as select distinct id,c from t
                                    *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

If your values are always less than 4k, you can use:

anbob@ANBOB>create or replace view v as select distinct id,to_char(c) new_c from t;
View created.

anbob@ANBOB>select * from v;
                  ID NEW_C
-------------------- ----------
                   1 a
-- where 				   
anbob@ANBOB>select * from t where c='a';
select * from t where c='a'
                      *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

Tip:
Comparison conditions compare one expression with another. The result of such a comparison can be TRUE, FALSE, or UNKNOWN.
Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on CLOB data.

read more oracle doc.

Another way, You can too use this function to avoid the error dbms_lob.substr()

anbob@ANBOB>select distinct dbms_lob.substr(c) new_c from t;

NEW_C
----------
a

Another way, use pl/sql to avoid the error

anbob@ANBOB>create or replace type typ_clob 
  2  is object (c clob,order member function equals(p_c typ_clob)
  3  return number);
  4  /

Type created.
anbob@ANBOB>create or replace type body typ_clob
  2  is
  3  order member function equals(p_c typ_clob) return number
  4   is
  5   begin
  6    return case when self.c is null and p_c.c is null then 0
  7           else nvl(dbms_lob.compare(self.c,p_c.c),1)
  8           end;
  9   end;
 10  end;
 11  /

Type body created.

anbob@ANBOB>select * from t;

                  ID C
-------------------- ----------
                   1 a

anbob@ANBOB>insert into t values (2,'a');
1 row created.

anbob@ANBOB>insert into t values (1,'a');
1 row created.

anbob@ANBOB>select * from t;
                  ID C
-------------------- ----------
                   1 a
                   2 a
                   1 a
				   
anbob@ANBOB>select distinct id,typ_clob(c) new_c from t;

                  ID NEW_C(C)
-------------------- ----------
                   1 TYP_CLOB('
                     a')

                   2 TYP_CLOB('
                     a')

anbob@ANBOB>with v as (select distinct id,typ_clob(c) new_c from t)
  2  select id,treat(new_c as typ_clob).c as c from v;

                  ID C
-------------------- ----------
                   1 a
                   2 a