Comparación de CLOBs de Oracle en la misma fila.

13 Jul, 2023 Programación 0

Tengo una tabla que contiene algunas columnas y dos CLOBS.

En algunos casos, ambos CLOBS contienen los mismos valores exactos y en otros casos contienen valores diferentes.
o uno o ambos CLOBS pueden estar vacíos o ser NULl.

He creado una función, que me gustaría llamar para comparar ambos CLOBS y determinar si los valores son ‘IGUALES’ o diferentes.

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’);

Resultado esperado

SEQ_NUM VAL CLOB1 CLOB2
1 1 aaaaaaaaaa aaaaaaaaaa IGUALES
2 1 aaaaa aaaaaaaaaa DIFERENTES
3 2 Xaaaaaaaaa aaaaaaaaaa DIFERENTES

Basándome en el resultado de la consulta anterior, si la salida de SHA512 muestra que la diferencia es la misma, quiero INSERTAR 1 fila en la nueva tabla a continuación. Si la salida es diferente, quiero INSERTAR 2 filas en la tabla siguiente.

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);

davy.ai

July 13, 2023 at 2:34 pm

Para comparar los dos CLOBs, crearía una nueva función que utilice la función HASH_SHA512 previamente creada para generar un hash para cada CLOB, y luego compararía los valores de hash resultantes. Si los valores de hash coinciden, los CLOBs contienen valores idénticos, y si no coinciden, los CLOBs contienen valores diferentes. Aquí tienes un ejemplo de la función:

CREATE OR REPLACE FUNCTION COMPARAR_CLOBS (
    psCLOB1 IN CLOB,
    psCLOB2 IN CLOB
    ) RETURN VARCHAR2 AS
    rHash1 RAW (512);
    rHash2 RAW (512);
BEGIN
    rHash1 := HASH_SHA512 (psCLOB1);
    rHash2 := HASH_SHA512 (psCLOB2);
    IF (rHash1 = rHash2) THEN
        RETURN 'IGUALES';
    ELSE
        RETURN 'DIFERENTES';
    END IF;
END COMPARAR_CLOBS;
/

Luego utilizaría esta función en una nueva consulta para generar la salida esperada mostrada en la pregunta:

SELECT 
    NUM_SEQ, VALOR, CLOB1, CLOB2, COMPARAR_CLOBS(CLOB1, CLOB2) AS COMPARACION_CLOBS
FROM 
    TABLA_X;

Esta consulta compara los valores de los CLOBs en CLOB1 y CLOB2 para cada fila y devuelve el resultado de la función COMPARAR_CLOBS como COMPARACION_CLOBS. Con esta consulta se puede generar la salida esperada mostrada en la pregunta.

Para insertar filas en TABLA_Z basándose en la comparación de los CLOBs, modificaría la consulta de la siguiente manera:

INSERT INTO TABLA_Z (VALOR, HASH_VALOR, CLOB_VALOR)
SELECT 
    VALOR, HASH_SHA512(CLOB1), CLOB1
FROM 
    TABLA_X
WHERE 
    COMPARAR_CLOBS(CLOB1, CLOB2) = 'IGUALES';

INSERT INTO TABLA_Z (VALOR, HASH_VALOR, CLOB_VALOR)
SELECT 
    VALOR, HASH_SHA512(CLOB1), CLOB1
FROM 
    TABLA_X
WHERE 
    COMPARAR_CLOBS(CLOB1, CLOB2) = 'DIFERENTES';

INSERT INTO TABLA_Z (VALOR, HASH_VALOR, CLOB_VALOR)
SELECT 
    VALOR, HASH_SHA512(CLOB2), CLOB2
FROM 
    TABLA_X
WHERE 
    COMPARAR_CLOBS(CLOB1, CLOB2) = 'DIFERENTES';

Esta consulta inserta filas en TABLA_Z basándose en la comparación de los CLOBs en TABLA_X. La primera instrucción INSERT inserta una única fila para cada par de CLOBs que son idénticos (COMPARAR_CLOBS devuelve “IGUALES”). Las segunda y tercera instrucciones INSERT insertan una fila para cada CLOB individual cuando son diferentes (COMPARAR_CLOBS devuelve “DIFERENTES”). La columna HASH_VALOR en TABLA_Z almacena el hash SHA512 del CLOB, y la columna CLOB_VALOR almacena el valor correspondiente del CLOB. Hay que tener en cuenta que esta consulta asume que HASH_SHA512 devuelve un valor de hash único para cada valor de CLOB distinto. Si este no es el caso, la columna HASH_VALOR deberá hacerse única utilizando una secuencia o algún otro mecanismo.

Answer

davy.ai

July 13, 2023 at 2:34 pm
Para comparar los dos CLOBs, crearía una nueva función que utilice la función HASH_SHA512 previamente creada para generar un hash para cada CLOB, y luego compararía los valores de hash resultantes. Si los valores de hash coinciden, los CLOBs contienen valores idénticos, y si no coinciden, los CLOBs contienen valores diferentes. Aquí tienes un ejemplo de la función:
```
CREATE OR REPLACE FUNCTION COMPARAR_CLOBS (
    psCLOB1 IN CLOB,
    psCLOB2 IN CLOB
    ) RETURN VARCHAR2 AS
    rHash1 RAW (512);
    rHash2 RAW (512);
BEGIN
    rHash1 := HASH_SHA512 (psCLOB1);
    rHash2 := HASH_SHA512 (psCLOB2);
    IF (rHash1 = rHash2) THEN
        RETURN 'IGUALES';
    ELSE
        RETURN 'DIFERENTES';
    END IF;
END COMPARAR_CLOBS;
/
```
Luego utilizaría esta función en una nueva consulta para generar la salida esperada mostrada en la pregunta:
```
SELECT 
    NUM_SEQ, VALOR, CLOB1, CLOB2, COMPARAR_CLOBS(CLOB1, CLOB2) AS COMPARACION_CLOBS
FROM 
    TABLA_X;
```
Esta consulta compara los valores de los CLOBs en CLOB1 y CLOB2 para cada fila y devuelve el resultado de la función COMPARAR_CLOBS como COMPARACION_CLOBS. Con esta consulta se puede generar la salida esperada mostrada en la pregunta.

Para insertar filas en TABLA_Z basándose en la comparación de los CLOBs, modificaría la consulta de la siguiente manera:
```
INSERT INTO TABLA_Z (VALOR, HASH_VALOR, CLOB_VALOR)
SELECT 
    VALOR, HASH_SHA512(CLOB1), CLOB1
FROM 
    TABLA_X
WHERE 
    COMPARAR_CLOBS(CLOB1, CLOB2) = 'IGUALES';

INSERT INTO TABLA_Z (VALOR, HASH_VALOR, CLOB_VALOR)
SELECT 
    VALOR, HASH_SHA512(CLOB1), CLOB1
FROM 
    TABLA_X
WHERE 
    COMPARAR_CLOBS(CLOB1, CLOB2) = 'DIFERENTES';

INSERT INTO TABLA_Z (VALOR, HASH_VALOR, CLOB_VALOR)
SELECT 
    VALOR, HASH_SHA512(CLOB2), CLOB2
FROM 
    TABLA_X
WHERE 
    COMPARAR_CLOBS(CLOB1, CLOB2) = 'DIFERENTES';
```
Esta consulta inserta filas en TABLA_Z basándose en la comparación de los CLOBs en TABLA_X. La primera instrucción INSERT inserta una única fila para cada par de CLOBs que son idénticos (COMPARAR_CLOBS devuelve “IGUALES”). Las segunda y tercera instrucciones INSERT insertan una fila para cada CLOB individual cuando son diferentes (COMPARAR_CLOBS devuelve “DIFERENTES”). La columna HASH_VALOR en TABLA_Z almacena el hash SHA512 del CLOB, y la columna CLOB_VALOR almacena el valor correspondiente del CLOB. Hay que tener en cuenta que esta consulta asume que HASH_SHA512 devuelve un valor de hash único para cada valor de CLOB distinto. Si este no es el caso, la columna HASH_VALOR deberá hacerse única utilizando una secuencia o algún otro mecanismo.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Comparación de CLOBs de Oracle en la misma fila.

Answer

davy.ai