Skip to Main Content
  • Questions
  • Convert clob to char to get distinct

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, jay.

Asked: September 21, 2018 - 12:47 pm UTC

Last updated: August 22, 2022 - 12:49 pm UTC

Version: 11g and 12c

Viewed 10K+ times! This question is

You Asked

Hii ASKTOM,
select distinct clob_column_name from table_name;
I get following error:-
ora-00932: inconsistent datatypes;expected-got CLOB

While i use to_char function
select distinct to_char(clob_column_name) from table_name;
then i get following error:-
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4870, maximum: 4000)

So how to solve this error?

and Chris said...

There are a few options:

Substr the lob

Use dbms_lob.substr to return the first 4k characters and take the distinct of that:

create table t (
  c1 clob
);

insert into t values ( 'xxx' );
insert into t values ( 'xxx' );
insert into t values ( 'yyy' );
commit;

select distinct dbms_lob.substr ( c1 , 4000, 1 ) 
from   t;

DBMS_LOB.SUBSTR(C1,4000,1)   
yyy                          
xxx 


Obviously you need to be reasonably sure these first N characters will be close enough to the distinct values.

Hash the lob

Generate hashes of the clobs using dbms_crypto. Use this as the partition by clause for row_number to find others with the same hash. Then filter to those where the row_number = 1:

with rws as (
  select row_number () over ( 
           partition by sys.dbms_crypto.hash ( c1, 1 ) order by rowid
         ) rn,
         t.*
  from   t
)
  select c1 from rws
  where  rn = 1;

C1    
xxx   
yyy  


Theoretically has a chance of false positives - i.e. hashing two different clobs to the same value. Unlikely to happen in practice.

DBMS_lob.compare

Add a unique row number to every row. Use dbms_lob.compare to find matching values. Discard those with a higher row number using not exists:

with rws as (
   select row_number () over ( 
            order by rowid
          ) rn,
          t.*
  from   t
)
  select * from rws r1
  where  not exists (
    select * from rws r2
    where  dbms_lob.compare ( r1.c1, r2.c1 ) = 0
    and    r1.rn > r2.rn
  );

RN   C1    
   1 xxx   
   3 yyy

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I might have a better/ more simple solution

Alix, August 17, 2022 - 5:13 pm UTC

My solution...

SELECT DISTINCT listagg(clob_column_name,'| ') within GROUP (ORDER BY unique_id) over (PARTITION BY unique_id) clob_column 
 FROM table_name;

Chris Saxon
August 22, 2022 - 12:49 pm UTC

How does this help? LISTAGG can't return a CLOB, so if the column stores values longer than the varchar2 limit you'll get an error.

Hash the lob +

emaN, August 22, 2022 - 2:21 pm UTC

Hash the lob + type
create or replace type clob_hash as object (
  lob clob,
  map member function hash return raw
)
/
create or replace type body clob_hash as
  map member function hash return raw is
  begin
    return dbms_crypto.hash(lob, dbms_crypto.hash_sh512);
  end;
end;
/

with t(n, c) as (select 1,to_clob('xxx') from dual
       union all select 2,to_clob('yyy') from dual
       union all select 3,to_clob('xxx') from dual
)
select tt.o.lob c, nn
from (
  select clob_hash(c) o, listagg(n,',') nn from t group by clob_hash(c)
) tt;

C          NN        
---------- ----------
xxx        1,3       
yyy        2         

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library