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[amazonify]::omakase::300:250[/amazonify]


Comments

  1. Quote

    You know, the thing about SQL is, that there is virtually nothing that can replace it.

    Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

Leave a Comment

(required)

(required)

Formatting Your Comment

The following XHTML tags are available for use:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

URLs are automatically converted to hyperlinks.