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
— end —