Oracle: DBMS_LOB.COMPARE

Prevent comparison error (ORA-00932, ORA-22848)

create table tq84_clob (
   id   integer,
   clb  clob
);
 
begin
   delete tq84_clob;
   insert into tq84_clob values (0,  null);
   insert into tq84_clob values (1,  empty_clob());
   insert into tq84_clob values (2, 'hello world');
   commit;
end;
/

The following statement runs without problems and returns 0:

select id from tq84_clob where clb is null;

The following to statements throw ORA-00932: inconsistent datatypes: expected - got CLOB (without improved error messages) or ORA-22848: cannot use CLOB type as comparison key with improved error messages:

select id from tq84_clob where clb = '';
select id from tq84_clob where clb =  empty_clob();
select id from tq84_clob where clb = 'hello world';

In order to compare a clob with string, dbms_lob.compare can be used:

select id from tq84_clob where dbms_lob.compare(clb, 'hello world') = 0;
select id from tq84_clob where dbms_lob.compare(clb,  empty_clob()) = 0;

Cleaning up:

drop table tq84_clob;

TODO

create table tq84_clob_compare (
   i  number primary key,
   c  clob
);

begin
   insert into tq84_clob_compare values ( 1, empty_clob());
   insert into tq84_clob_compare values ( 2, empty_clob());
   insert into tq84_clob_compare values ( 3, empty_clob());
   insert into tq84_clob_compare values ( 4, empty_clob());
   insert into tq84_clob_compare values (20, empty_clob());
   
   insert into tq84_clob_compare values (99, empty_clob());
   insert into tq84_clob_compare values (99, empty_clob());

   commit;
end;
/

declare
  c_ clob;
begin

  for j in 1 .. 4 loop

      select c into c_ from tq84_clob_compare where i = j;    

         if j = 1 then dbms_lob.append(c_, 'one'  ); 
      elsif j = 2 then dbms_lob.append(c_, 'two'  );
      elsif j = 3 then dbms_lob.append(c_, 'three');
      elsif j = 4 then dbms_lob.append(c_, 'four' ); end if;

  end loop;

  select c into c_ from tq84_clob_compare where i = 20;

  dbms_lob.append(c_, 'two');
end;
/

commit;

-- first one smaller than second one returns -1.
-- first one greater than second one returns  1.
-- first one equal to second one returns 0.
select t.i, u.i, dbms_lob.compare(t.c, u.c) 
  from tq84_clob_compare t,
       tq84_clob_compare u;


drop table tq84_clob_compare;

Oracle: DBMS_LOB.COMPARE

Prevent comparison error (ORA-00932, ORA-22848)

TODO

See also