Compare tables with the minus operator, even if they contain clobs and/or blobs.

I wanted to compare the content of two tables with identical layout quickly with the minus
set operator, but ran into a couple of errors, caused by clob and blob colums.
As I found out, set operators ( union, minus, intersect ) in
combination with clobs or blobs, are not supported, so this explained the problems I encountered.

But there’s a way to use this combination anyway, as long as you’re satisfied with
comparing just the first 2000 bytes, and the length of your clobs/blobs.

First of all, to show this work around, I will create some tables containing at least one clob and one blob:

SQL> desc mdsys.all_sdo_styles
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(32)
 NAME                                      NOT NULL VARCHAR2(32)
 TYPE                                      NOT NULL VARCHAR2(32)
 DESCRIPTION                                        VARCHAR2(4000)
 DEFINITION                                NOT NULL CLOB
 IMAGE                                              BLOB
 GEOMETRY                                           MDSYS.SDO_GEOMETRY

SQL> create table T1 as select owner, name, type, description, definition, image from mdsys.all_sdo_styles;

Table created.

SQL> create table T2 as select * from T1; 

Table created.

SQL> select count(*) from T1;

  COUNT(*)
----------
        78

SQL>  create table T3 as select * from T1 where rownum < 11;

Table created.

Now try using the minus set-operator to check if T1 is identical to T2, which it should show
by not returning any rows, but instead throws an error:

SQL> select * from T1 minus select * from T2;
select * from T1 minus select * from T2
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got BLOB

Use the work around function – the code is provided at the end of this blog – , avoiding the ORA-00932 error,
returning 0 when tables match, and 1 when not so:

SQL> select diff_tables('T1','T2') from dual;

DIFF_TABLES('T1','T2')
----------------------
                     0

SQL> select diff_tables('T1','T3') from dual;

DIFF_TABLES('T1','T3')
----------------------
                     1

SQL>

As I mentioned before, this compare isn’t completely full proof… if you want full proof, check
out the dbms_lob.compare function.
If you wonder why I didn’t select the GEOMETRY column of datatype MDSYS.SDO_GEOMETRY
when creating tables T1, T2 and T3… well, of course I did, just testing against
better judgment, and it threw the following error:

ORA-22901: cannot compare VARRAY or LOB attributes of an object type

This shows the limitations of diff_tables… only use this function on tables with identical layout, and
columns of “set operator supported datatypes” , and with or without clobs/blobs.

Here is the code off diff_tables:

SQL> set linesize 300 pagesize 0 heading off    
SQL> select text from user_source where type='FUNCTION' and name ='DIFF_TABLES' order by line;
FUNCTION Diff_Tables
    ( P_name_tab1   VARCHAR2
    , P_name_tab2   VARCHAR2
    , P_owner_tab1  VARCHAR2 := USER
    , P_owner_tab2  VARCHAR2 := USER )
  RETURN PLS_INTEGER
IS

  v_tab1   VARCHAR2(100)  := UPPER(TRIM(P_owner_tab1||'.'||P_name_tab1));
  v_tab2   VARCHAR2(100)  := UPPER(TRIM(P_owner_tab2||'.'||P_name_tab2));
  v_clist  VARCHAR2(4000) := NULL;
  v_col    VARCHAR2(100);
  cntr     PLS_INTEGER := 0;
  cnt1     NUMBER := 0;
  cnt2     NUMBER := 0;
  v_ret    PLS_INTEGER := 0;

BEGIN

-- setup the column list
  for x in ( select column_name
                  , data_type
             from dba_tab_columns
             where owner||'.'||table_name = v_tab1
             order by column_id )
  loop
    cntr := cntr + 1;
    if x.data_type in ('CLOB','BLOB') then
      v_col := 'DBMS_LOB.SUBSTR('||x.column_name||',2000), DBMS_LOB.GETLENGTH('||x.column_name||')';
    else
      v_col := x.column_name;
    end if;
    if cntr != 1 then
      v_col := ','||v_col;
    end if;
    v_clist := v_clist||v_col;
  end loop;

-- compare
  EXECUTE IMMEDIATE 'select count(*) from ( select '||v_clist||' from '||v_tab1||' minus select '||v_clist||' from '||v_tab2||' )' INTO cnt1;
  EXECUTE IMMEDIATE 'select count(*) from ( select '||v_clist||' from '||v_tab2||' minus select '||v_clist||' from '||v_tab1||' )' INTO cnt2;

-- return 0 if tables match, and 1 if tables differ.
  if (cnt1+cnt2) > 0 then
    v_ret := 1;
  end if;

  return v_ret;

END Diff_Tables;

50 rows selected.

SQL>