Oracle 9i Clob Resolved
Working with CLOBs has been very difficult. The task was to extract all unique messages that were in LDR2 and not in LDR into a UNIQUE table.
Set commands such as,MINUS, INTERSECT, =, etc, won’t work with CLOBs datatypes. But using dbms_lob.getlength, dbms_lob.Compare, and dbms_Utility.Get_hash_value. This sql OUTER-JOIN finally worked:
insert into seebeyond.RAD_MESSAGES_UNIQUE (SELECT seebeyond.RAD_MESSAGES_LDR2.MESSAGES, seebeyond.RAD_MESSAGES_LDR2.ID FROM seebeyond.RAD_MESSAGES_LDR2 LEFT OUTER JOIN seebeyond.RAD_MESSAGES_LDR ON (seebeyond.RAD_MESSAGES_LDR2.HASH = seebeyond.RAD_MESSAGES_LDR.HASH) AND (seebeyond.RAD_MESSAGES_LDR2.MSGLEN = seebeyond.RAD_MESSAGES_LDR.MSGLEN) AND (dbms_lob.Compare(seebeyond.RAD_MESSAGES_LDR2.MESSAGES,seebeyond.RAD_MESSAGES_LDR.MESSAGES)=0) WHERE seebeyond.RAD_MESSAGES_LDR.ID IS NULL)
SQL> select count(*) from RAD_MESSAGES_LDR2;
COUNT(*)
———-
744923
SQL> select count(*) from RAD_MESSAGES_LDR;
COUNT(*)
———-
713316
SQL> select count(*) from RAD_MESSAGES_UNIQUE;
COUNT(*)
———-
31624