Previously wrote a script that passed dbms_lob.getlength to the length of the lob, later through practice found a bit of a problem, found a lot of length of the same lob but the content may be different, Oracle is currently only using Dbms_ Lob.compare to the LOB field, but this one limitation is that it is not possible to use Dblink to compare the lob to two libraries, here is an example script that uses two tables tb1 and TB2 to compare the LOB with the primary key ID
Prints the ID number if the LOB inconsistency is found
. /home/oracle/.bash_profilesqlplus-silent test/test <<eofset serveroutput onset pagesize 0 feedback off Verify off H Eading off Echo offdeclarecursor C1 is select ID from TEST.TB1;VARC1 blob;varc2 blob;ln number (4); Beginfor v1 in C1 loopselect data to varC1 from test.tb1 where id = v1.id;select data to varC2 from TEST.TB2 where ID = V1.id; ln: = Dbms_lob.compare (VARC1,VARC2); if ln <>0 thendbms_output.put_line (v1.id); end If;end Loop; end;/
Using the Dbms_lob.compare LOB field