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:

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:

2 Comments

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/