Tuesday 7 August 2012

Getting distinct values from a column of data type CLOB

-- What I have  learnt today:  
bkd@Oracle>drop table t;

Table dropped.

bkd@Oracle>create table t
  2  as
  3
bkd@Oracle>create table t (col clob);

Table created.

bkd@Oracle>insert into t
  2  (select 'A' from dual
  3  UNION ALL
  4  select 'B' from dual
  5  union all
  6  select 'C' from dual
  7  union all
  8  select 'A' from dual );

4 rows created.

bkd@Oracle>select col from t;

COL
--------------------------------------------------------------------------------
A
B
C
A

bkd@Oracle>--Remove duplicate--
bkd@Oracle>select distinct col from t;
select distinct col from t
                *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
You can not use DISTINCT in column of data type CLOB.So, how we are going to get the distinct values out of it?

Workaround:

bkd@Oracle>select a.col from t a
  2  where not exists ( select 1 from t b
  3                     where a.rowid < b.rowid
  4                     and dbms_lob.compare(a.col,b.col)=0);

COL
--------------------------------------------------------------------------------
B
C
A

Regards
BKD

No comments:

Post a Comment