Oracle - comparing CLOB

From ExxtremeWiki
Jump to: navigation, search

Oracle - comparing CLOB[edit]

[edit] Comparing CLOBs

What if you have two CLOB columns to compare? For example the maxpresentation table's presentation field...

A construct like


SELECT someColumn
FROM tab1
WHERE col1 != col2

will not work.

This is what you will need to do to compare (using the maxpresentation table as an example):

SELECT a.app, a.presentation, b.presentation
   FROM maxpresentation a, maxpresentation_temp b
   WHERE dbms_lob.compare(a.presentation, b.presentation) != 0

The return value is non-zero if the comparison fails. (or no rows selected)

I tested this by making a copy of the maxpresentation table

CREATE TABLE maxpresentations_temp AS SELECT * FROM maxpresentation

and running it against the two tables. The first time I did this it did not complete and I discovered that there were missing text indexes on the Virtual Machine I was testing on (I'll add those references in a bit) - once I had all the right indexes in place it took around 1.4 seconds to complete... [edit] See also

   http://en.wikipedia.org/wiki/Clob
   Binary large object 

Template:Database-stub

nl:CLOB pl:CLOB

Update 20150218 - Will "another example" for use in Benthic's Golden6[edit]

/*
** Perform comparison of maxpresentation and autoscript clobs via temp table and dblink
*/
 
-- SELECT * FROM maximo.autoscript@exxtreme.com;
 
CREATE GLOBAL TEMPORARY TABLE TEMP_autoscript
ON COMMIT DELETE ROWS
AS
   SELECT * FROM maximo.autoscript@ exxtreme.com
;
 
--Use this statement if you need to refresh the TEMP_TEXT_TABLE table
INSERT INTO TEMP_autoscript
SELECT * FROM maximo.autoscript@ exxtreme.com;
 
SELECT * FROM temp_autoscript;
 
SELECT a.autoscript, a.SOURCE AS Source_Dev1, b.SOURCE  AS Source_Stage
      FROM maximo.autoscript a
      JOIN temp_autoscript b ON a.autoscript = b.autoscript
      WHERE dbms_lob.compare(a.SOURCE, b.SOURCE) != 0
/
 
export csv "c:\app\DBComparison\Export_Dev1toStage\autoscript_source.csv"
/
 
DROP TABLE TEMP_autoscript;
 
CREATE GLOBAL TEMPORARY TABLE TEMP_maxpresentation
ON COMMIT DELETE ROWS
AS
   SELECT * FROM maximo.maxpresentation@exxtreme.com
;
 
--Use this statement if you need to refresh the TEMP_TEXT_TABLE table
INSERT INTO TEMP_maxpresentation
SELECT * FROM maximo.maxpresentation@exxtreme.com;
 
SELECT * FROM temp_maxpresentation;
 
SELECT a.app, a.presentation AS presentation_Dev1, b.presentation  AS presentation_Stage
      FROM maximo.maxpresentation a
      JOIN temp_maxpresentation b ON a.app = b.app
      WHERE dbms_lob.compare(a.presentation, b.presentation) != 0
/
 
export csv "c:\app\DBComparison\Export_Dev1toStage\maxpresentation_source.csv"
/
 
DROP TABLE TEMP_maxpresentation;


--wkh 22:08, 5 April 2009 (UTC) Categories: Oracle, Data types