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>
Well, you better forget about my last comment… the ORA_HASH function, used in the way I do, isn’t consistent in the result value.
And if result values differ in time for identical input, comparing two tables that use this function will (almost ) always result in 1 ( tables differ ).
See for more info about the ORA_HASH function:
http://jonathanlewis.wordpress.com/2009/11/21/ora_hash-function/
I discovered that there is an easy ( without using dbms_lob.compare ) full proof solution, by applying
the ORA_HASH function ( available as of 10g Release 1 ).
The docs – http://docs.oracle.com/cd/B13789_01/server.101/b10759/functions097.htm – mention explicitly that there is “no restrictions on the type or length of data”, so I tested it on LONG’s too.
And indeed… no problem at all.
Here is the the changed code, using the ORA_HASH function now:
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’,’LONG’) then
v_col := ‘ORA_HASH(‘||x.column_name||’)’;
— 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>