Oracle comparing clobs in same row-CodePudding

I have a table that contains some columns and two CLOBS.

In some cases both the CLOBS contain the same exact values and in other cases they contain different values. or one or both CLOBS can be empty or NULl.

I created a function, which I would like to call to compare both CLOBS to determine if the values are 'SAME' or different.


CREATE or REPLACE FUNCTION HASH_SHA512 (
    psINPUT IN VARCHAR2
    ) RETURN VARCHAR2 AS
    rHash RAW (512);
    BEGIN
    rHash := DBMS_CRYPTO.HASH (TO_CLOB (psINPUT), 
dbms_crypto.HASH_SH512);
    RETURN (LOWER (RAWTOHEX (rHash)));
    END HASH_SHA512;
/



CREATE table table_x(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
val NUMBER, 
clob1 CLOB,
clob2 CLOB);


insert into table_x (val, clob1, clob2) values (1,'aaaaaaaaaa','aaaaaaaaaa');

insert into table_x (val, clob1, clob2) values (1,'aaaaa','aaaaaaaaaa');

insert into table_x (val, clob1, clob2) values (2,'Xaaaaaaaaa','aaaaaaaaaa');

Expected output 

SEQ_NUM    VAL    CLOB1    CLOB2
1    1    aaaaaaaaaa    aaaaaaaaaa  SAME
2    1    aaaaa    aaaaaaaaaa DIFFERENT 
3    2     Xaaaaaaaaa    aaaaaaaaaa DIFFERENT

Based on the output of the above query if the SHA512 output shows the difference to be the same I want to INSERT 1 row into the new table below. If the output is different, I want to INSERT 2 rows into the table below.

CREATE table table_z(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
val NUMBER, 
hash_val VARCHAR2(1000) not NULL PRIMARY KEY, 
clob_val CLOB);

CodePudding user response：

It does not make very much sense to write your own function to compare two LOBs since Oracle already has a COMPARE function in the DBMS_LOB package. It can easily be leveraged in your query like this:

SELECT x.*,
       CASE DBMS_LOB.compare (x.clob1, x.clob2) WHEN 0 THEN 'SAME' ELSE 'DIFFERENT' END    AS comparison
  FROM table_x x;

   SEQ_NUM    VAL         CLOB1         CLOB2    COMPARISON
__________ ______ _____________ _____________ _____________
         1      1 aaaaaaaaaa    aaaaaaaaaa    SAME
         2      1 aaaaa         aaaaaaaaaa    DIFFERENT
         3      2 Xaaaaaaaaa    aaaaaaaaaa    DIFFERENT

To insert the different CLOBs into table_z you could use a SQL statement like this:

INSERT INTO table_z (val, hash_val, clob_val)
    SELECT x1.val, DBMS_CRYPTO.HASH (x1.clob1, 6                          /*DBMS_CRYPTO.HASH_SH512*/
                                                ), x1.clob1
      FROM table_x x1
    UNION ALL
    SELECT x2.val, DBMS_CRYPTO.HASH (x2.clob2, 6                          /*DBMS_CRYPTO.HASH_SH512*/
                                                ), x2.clob2
      FROM table_x x2
     WHERE DBMS_LOB.compare (x2.clob1, x2.clob2) <> 0;

Currently, the PRIMARY KEY you have defined on table_z would prevent the sample records you provided from inserting because the same CLOB appears on multiple rows. You can either remove that primary key or add additional columns to the primary key to allow the data to be inserted.

CodePudding user response：

You can sovle this with a Trigger at "table_x".

https://docs.oracle.com/cd/B13789_01/appdev.101/b10795/adfns_tr.htm

→ In the PL-SQL-Code of this Trigger, you can distinguish between the cases "SAME" and "DIFFERENT".

This Trigger should look like:

CREATE OR REPLACE TRIGGER compare_table_x
  BEFORE INSERT ON table_x
  FOR EACH ROW
DECLARE
    compare table_x.compare;
BEGIN
    IF compare = 'SAME' THEN
      INSERT ...
    ELSE
      INSERT ... 
      INSERT ...
    END IF;
END;