You Asked

Hello Great Asktom Team,

We can't do a minus query with a clob column.

So what are the alternatives.

I have ETL process which finds deltas of table based on minus. Can't do that with a column having data type clob

So how to do it in pure SQL

How to do it in plsql.

I tried using to char on clob in SQL ...limitation of 4000 characters only. Clobs can be greator than 32k length.

I know in plsql to char goes upto 32k.

Thanks for taking my question.

and Connor said...

How about using a hash value ? eg Here's a table where the first 20 rows have the same clob in two columns, and then a different clob across the two columns in the next 20 rows.


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x int, y clob , z clob);

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    c clob;
  3  begin
  4    dbms_lob.createtemporary(c,true);
  5
  6    c := 'Hello there.  This is some text to be repeated';
  7    for i in 1 .. 12 loop
  8       c := c || c;
  9    end loop;
 10
 11    dbms_output.put_line(length(c));
 12
 13    for i in 1 .. 20 loop
 14      insert into t values (i,c ,c);
 15      insert into t values (i+100,c ,c||i);
 16    end loop;
 17    commit;
 18
 19    dbms_lob.freetemporary(c);
 20  end;
 21  /
188416

PL/SQL procedure successfully completed.

SQL>
SQL> select x,
  2         case when dbms_crypto.hash(y,2) = dbms_crypto.hash(z,2) then 'same' else 'different' end tag
  3  from t
  4  order by x;

         X TAG
---------- ---------
         1 same
         2 same
         3 same
         4 same
         5 same
         6 same
         7 same
         8 same
         9 same
        10 same
        11 same
        12 same
        13 same
        14 same
        15 same
        16 same
        17 same
        18 same
        19 same
        20 same
       101 different
       102 different
       103 different
       104 different
       105 different
       106 different
       107 different
       108 different
       109 different
       110 different
       111 different
       112 different
       113 different
       114 different
       115 different
       116 different
       117 different
       118 different
       119 different
       120 different

40 rows selected.

SQL>
SQL>

Rating

(1 rating)

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

Comments

dbms_lob.compare

Robert, July 11, 2016 - 9:29 am UTC

You can also use the dbms_lob.compare function:

select 
  x,
  case when dbms_lob.compare(y, z) = 0 then 'same' else 'different' end tag
from t
order by x
;

July 12, 2016 - 2:20 am UTC

Good catch, and a bit snappier as well - the below test on 400 clobs at 1.5meg each

SQL> set timing on
SQL> select tag, count(*)
  2  from (
  3  select x,
  4       case when dbms_crypto.hash(y,2) = dbms_crypto.hash(z,2) then 'same' else 'different' end tag
  5  from t
  6  )
  7  group by tag;

TAG         COUNT(*)
--------- ----------
different        200
same             200

Elapsed: 00:00:05.95
SQL>
SQL>
SQL> select tag, count(*)
  2  from (
  3  select
  4    x,
  5    case when dbms_lob.compare(y, z) = 0 then 'same' else 'different' end tag
  6  from t
  7  )
  8  group by tag;

TAG         COUNT(*)
--------- ----------
different        200
same             200

Elapsed: 00:00:03.39
SQL>

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

dbms_lob.compare

More to Explore

CLOB/BLOB/etc

Clob and minus - alternatives for Diff Deltas

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

dbms_lob.compare

More to Explore

CLOB/BLOB/etc