Para quien no lo sepa, trabajar como desarrollador es aventurarse entre selvas de funciones y procedimientos no documentados, funcionalidades obsoletas (o lo que en nuestra jerga llamamos deprecadas), y un sinfín de infortunios que hacen que nuestro trabajo no sea aburrido (por no decir que lo hacen tortuoso y doloroso). Lo bueno es que cuando encontramos alternativas bastante rebuscadas pero funcionales, lo festejamos como gol de mediacancha :)
Empresa XXXXXX cuenta con dos bases de datos que necesitan comunicarse entre sí: una SQL Server (llamémosle origen) y la otra Oracle (destino). Al “sincronizar” los datos, estos se establecen como “información definitiva” en destino. Para establecer la comunicación, se utiliza DG4ODBC (Database Gateway for ODBC). Aquí se encuentra una guía oficial para la configuración de la pasarela (o puerta de enlace) de Oracle para SQL Server (Windows).
VARCHAR2 cuenta con una longitud máxima de 4000 bytes o caracteres si MAX_STRING_SIZE = STANDARD, o de 32767 bytes o caracteres si MAX_STRING_SIZE = EXTENDED (esto configurable desde la versión 12c). Si se necesita almacenar cadenas de mayor longitud, se puede utilizar CLOB o BLOB.CLOB ni BLOB, se puede utilizar el equivalente más cercano que es VARCHAR(MAX), cuyo valor máximo es de 2^31-1 bytes (2 GB).Por criterios arbitrarios, se decidió almacenar en Oracle las cadenas en formato BLOB (Binary Large Object) en lugar de CLOB (Character Large Object), este último como más recomendable.
LONG LONG LONG…
Oracle promete desde, al menos, la versión 8i, descontinuar LONG en una versión posterior de Oracle. Aún así, hasta ahora (2023) tenemos que lidiar con este tipo de dato (minado de restricciones, por cierto).
Leyendo el manual, en la tabla de mapeo de tipos de datos y restricciones, se observa que SQL_LONGVARCHAR de ODBC se traduce en Oracle a LONG, siempre y cuando el campo cuente con una longitud de entre 4000 y 32740 caracteres, o que cuya longitud sea de hasta 32767 caracteres si Oracle utiliza el VARCHAR extendido (32k). La observación menciona: Si una implementación ANSI SQL define un valor grande para la longitud máxima de los datos VARCHAR, es posible que ANSI VARCHAR se mapee a SQL_LONGVARCHAR y a LONG de Oracle.
Desde SQL Server el tipo de dato del campo era VARCHAR(MAX). Hasta cierto punto se podía manipular tal campo en Oracle, pero si la cadena original excedía los 32767 caracteres, no era posible leer la información. Y empezó la investigación.
Para los ejemplos, utilizo “VI_VistaRemota” para el nombre de la vista en SQL Server, y “DBLINKSQLSERVER” para el nombre del DBLink. Los campos efectivamente son escritos entrecomillados para realizar la consulta.
El caso de uso es con una cadena de 41635 caracteres. Esta consulta de prueba podía realizarse sin problemas:
SELECT "CampoRemoto"
FROM VI_VistaRemota@DBLINKSQLSERVER
WHERE "IdCampo" = <id_del_campo>
Y esta también, salvo que se descomente la línea en donde se imprime el resultado, ya que devuelve el error ORA-06502 (PL/SQL: numeric or value error: character string buffer too small, o en su mala traducción, error : buffer de cadenas de caracteres demasiado pequeño numérico o de valor):
DECLARE
resultado LONG;
BEGIN
SELECT "CampoRemoto"
INTO resultado
FROM VI_VistaRemota@DBLINKSQLSERVER
WHERE "IdCampo" = <id_del_campo>;
-- DBMS_OUTPUT.PUT_LINE(resultado);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM); -- Como es una prueba, elijo imprimir el error en lugar de hacer RAISE_APPLICATION_ERROR
END;
Como el caso real en Empresa XXXXXX es con SQL dinámico, ya que se cuenta con más de un DBLink, haciendo lo siguiente como prueba:
DECLARE
TYPE R_REMOTO IS RECORD(
campo_remoto LONG);
TYPE T_REMOTO IS TABLE OF R_REMOTO INDEX BY PLS_INTEGER;
l_remoto T_REMOTO;
v_dblink VARCHAR2(100) := 'DBLINKSQLSERVER';
v_id_campo NUMBER := < id_campo >;
BEGIN
EXECUTE IMMEDIATE q'[SELECT "CampoRemoto" campo_remoto FROM VI_VistaRemota@]' || v_dblink || q'[ WHERE "IdCampo" =]' || v_id_campo BULK COLLECT
INTO l_remoto;
FOR ind IN 1 .. l_remoto.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(ind); -- Imprime el índice actual, solo para no escribir "NULL;"
END LOOP;
DBMS_OUTPUT.PUT_LINE(LENGTH(l_remoto(1).campo_remoto)); -- Imprime la longitud del campo
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM); -- Como es una prueba, elijo imprimir el error en lugar de hacer RAISE_APPLICATION_ERROR
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM); -- Como es una prueba, elijo imprimir el error en lugar de hacer RAISE_APPLICATION_ERROR
END;
Al imprimir la longitud del campo, se muestra 32760, es decir: la cadena ha sido truncada.
Otras pruebas:
LONG_TO_CLOB: truncaba la cadena a 32760 caracteres.DBMS_XMLGEN o crear un package LONG2CLOB: mismo resultado que el ítem anterior.INSERT... SELECT: Al intentar insertar una cadena LONG en un campo de tipo CLOB (o BLOB) aparecía el error ORA-00997 (uso no válido del tipo de dato LONG, o en inglés, illegal use of LONG datatype).TO_LOB()]: mismo número de error que el ítem anterior.TO_CLOB() o TO_BLOB()]: aparece el error ORA-00932 (tipos de dato inconsistentes: se esperaba CHAR se ha obtenido LONG, o en inglés, inconsistent datatypes: expected CHAR got LONG).Si creyeron que se le pudo dar solución desde el lado de Oracle… Se equivocaron :( Yo sí tenía acceso a la base de datos de SQL Server (y lo resalto porque otros usuarios en la red no contaron con la misma suerte en su momento).
Inicialmente busqué cómo trocear cadenas para devolverlas como filas, en orden, en SQL Server. La mayoría de los códigos que encontré fueron utilizando delimitadores, pero este no era el caso, ya que lo que necesitaba era retornar cadenas con una longitud fija (salvo que la última cadena cuente con menos caracteres de lo esperado, claro está). De todos modos, inicialmente le eché ojo a este post que muestra cómo convertir cadenas delimitadas en filas en SQL Server y lo había descartado porque había conceptos que no comprendía en ese momento… Hasta que un término en particular me llamó la atención: Tally Table. Busqué información relacionada, y encontré un post (en español 😭 ✨) que lo explica: Creación de tablas numéricas auxiliares (tally tables).
Leyéndolo, encontré a un conocido mío: la cláusula WITH (CTE: Common Table Expression, Expresión de Tabla Común) pero con un concepto distinto: CTE recursivo. Desde allí ya no me fue difícil realizar una versión propia de uno de los ejemplos del primer enlace que cité aquí, ya que ahora sí comprendía qué hacía.
Cabe aclarar que quise crear un procediento almacenado donde pasara como argumento la cantidad de caracteres que deseaba que contenga cada cadena retornada, pero como no lograba que me devuelva correctamente el resultado (¡devolvía solo un caracter!) al llamar al SP con DBMS_HS_PASSTHROUGH, entonces decidí crear una vista (!) que retorne todas las cadenas fragmentadas de una tabla en particular (en este caso, es otra vista). Aquí, se decidió arbitrariamente que la longitud de cada cadena sería de hasta 32700 caracteres.
En los comentarios del código figura la explicación del mismo. La vista se denomina VI_FragmentosCadena.
CREATE OR ALTER VIEW VI_FragmentosCadena
AS
/*
### Funcionamiento ###
* Se retorna cada fragmento de la cadena con un CTE recursivo.
** La primera parte del CTE consiste en obtener la cadena original (en este caso, ColumnaCadena de la vista VI_Vista).
** La segunda parte del CTE consiste en agregar una condición de terminación. En este caso, la función recursiva finaliza cuando la cadena (en este caso, CadenaAFragmentar) ya no contiene caracteres restantes.
** En cada iteración, CadenaAFragmentar va acortándose.
** Los argumentos del CTE son el id de la fila, el orden (sucesión de números desde 1 hasta n, donde n depende de la cantidad de veces en que se fragmentó la cadena), el "remanente" de la cadena troceada, y la cadena original.
### Funciones utilizadas en CTE ###
* LEFT(cadena, <cantidad_caracteres>): Extrae <cantidad_caracteres> cantidad de caracteres de <cadena>, iniciando desde la izquierda.
* STUFF(<cadena>, <inicio>, <cantidad_caracteres>, <nueva_cadena>): Toma <cantidad_caracteres> cantidad de caracteres de <cadena>, iniciando desde la posición <inicio>, y reemplaza su valor por <nueva_cadena>.
### Referencias
- https://www.saurabhmisra.dev/sql-server-convert-delimited-string-into-rows/
- https://geeks.ms/lmblanco/2014/09/01/creacin-de-tablas-numricas-auxiliares-tally-tables-1/
- https://stackoverflow.com/a/5493616
### Información relacionada
- CTE (Common Table Expression)
- Tally Tables
*/
WITH CTE_Split(FilaId, Orden, ValoresSplit, CadenaAFragmentar) AS
(
-- miembro ancla
SELECT
FilaOriginalId,
1 AS Orden, -- 1 es el valor inicial, y en cada iteración se incrementará en 1
LEFT(CAST(ColumnaCadena AS varchar(MAX)), 32700),
STUFF(CAST(ColumnaCadena AS varchar(MAX)), 1, 32700, '')
FROM VI_Vista
UNION ALL
-- miembro recursivo
SELECT
FilaId,
Orden + 1,
LEFT(CadenaAFragmentar, 32700),
STUFF(CadenaAFragmentar, 1, 32700, '')
FROM CTE_Split
-- condición de terminación
WHERE CadenaAFragmentar > ''
)
-- invocar al CTE y generar el conjunto de resultados finales
SELECT FilaId, Orden, ValoresSplit
FROM CTE_Split;
GO
Ya en Oracle, se creó una función que recibe como argumentos el id del registro en cuestión y el DBLink, y retorna un BLOB como resultado (incluso puede acortarse el código para retornar un CLOB en su lugar), el cual es la cadena reensamblada. Aquí se llama a la vista creada en SQL Server: a través del WHERE se filtra el id deseado, y a través del ORDER BY se obtienen los fragmentos de cadena ordenados.
En esta función, se hace uso de varios subprogramas del paquete DMBS_LOB.
Es fundamental conservar el orden de origen, es decir (en este caso): si las columnas devueltas por la vista son FilaId (1), Orden (2) y ValoresSplit (3), el
SELECTdebe hacerse en tal orden (omitiéndose o no una que otra columna, como FilaId en el ejemplo). Anteriormente en la función el orden de las columnas era ValoresSplit (3) y luego Orden (2), provocando que la consulta demore entre 8 y 10 segundos en ejecutarse (que para cientos de registros, es 🐢)
CREATE OR REPLACE FUNCTION CADENA_REENSAMBLADA(V_ID_FILA NUMBER
,V_DBLINK VARCHAR2) RETURN BLOB IS
VRESULTADO BLOB;
VCADENA_BLOB BLOB;
VCADENA_CLOB CLOB;
--------- Para comprender qué son estos parámetros, ver https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i1020356
O1 INTEGER; -- offset del LOB destino
O2 INTEGER; -- offset del LOB origen
C INTEGER; -- contexto de lenguaje
W INTEGER; -- mensaje de advertencia
---------
TYPE CADENA_EXTENSA IS RECORD(
--No cambiar el orden de las columnas al capturarlas en el bulk collect, de lo contrario da error
--También mantener el orden de las columnas según el origen, caso contrario la consulta es muy lenta
ORDEN NUMBER
,CADENA_FRAGMENTADA LONG);
TYPE T_CADENA_EXTENSA IS TABLE OF CADENA_EXTENSA INDEX BY PLS_INTEGER;
L_CADENA_EXTENSA T_CADENA_EXTENSA; -- La variable L_CADENA_EXTENSA es de tipo T_CADENA_EXTENSA
BEGIN
BEGIN
-- Sí o sí se debe agregar en el select la columna con la cual se hace el order by, en caso contrario aparece el error "la base de datos xxxxxxx no soporta order by en este contexto" (database does not support ORDER BY in this context)
EXECUTE IMMEDIATE q'[SELECT "Orden" ORDEN
, "ValoresSplit" CADENA_FRAGMENTADA
FROM VI_FragmentosCadena@]' || V_DBLINK || q'[
WHERE "FilaId" =]' || V_ID_FILA || q'[ ORDER BY Orden]' BULK COLLECT
INTO L_CADENA_EXTENSA;
-- Se recorren los resultados
FOR IND IN 1 .. L_CADENA_EXTENSA.COUNT
LOOP
-- Se opera sobre el resultado devuelto solo si no es nulo
IF (L_CADENA_EXTENSA(IND).CADENA_FRAGMENTADA IS NOT NULL) THEN
IF IND = 1 THEN -- Se crea el temporary en la primera iteración
DBMS_LOB.CREATETEMPORARY(VCADENA_CLOB, TRUE); -- TRUE indica que el LOB debe leerse en la caché del búfer
END IF;
-- Se concatena a VCADENA_CLOB el contenido de CADENA_FRAGMENTADA
DBMS_LOB.APPEND(VCADENA_CLOB, L_CADENA_EXTENSA(IND).CADENA_FRAGMENTADA);
END IF;
END LOOP;
-------- Esta sección no es necesaria si se desea retornar un CLOB
-- Se convierte a BLOB solo si VCADENA_CLOB no es nulo
IF VCADENA_CLOB IS NOT NULL THEN
O1 := 1;
O2 := 1;
C := 0;
W := 0;
BEGIN
-- Se crea el temporary y luego se convierte a BLOB
DBMS_LOB.CREATETEMPORARY(VCADENA_BLOB, TRUE); -- TRUE indica que el LOB debe leerse en la caché del búfer
DBMS_LOB.CONVERTTOBLOB(VCADENA_BLOB, VCADENA_CLOB, LENGTH(VCADENA_CLOB), O1, O2, 0, C, W);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-25000, 'Error al intentar hacer la conversión (DBMS_LOB): ' || SQLERRM);
END;
END IF;
--------
-- Se asigna la cadena convertida a BLOB al resultado a retornar
VRESULTADO := VCADENA_BLOB;
-- Si se desea retornar el CLOB:
-- VRESULTADO := VCADENA_CLOB;
BEGIN
-- Se liberan ambos temporary
IF VCADENA_CLOB IS NOT NULL THEN
DBMS_LOB.FREETEMPORARY(VCADENA_CLOB);
END IF;
IF VCADENA_BLOB IS NOT NULL THEN
DBMS_LOB.FREETEMPORARY(VCADENA_BLOB);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20080, 'Error al liberar TEMPORARY (DBMS_LOB): ' || SQLERRM);
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Cursor sin datos: ' || L_CADENA_EXTENSA.COUNT);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20090, 'Error en EXECUTE IMMEDIATE: ' || SQLERRM);
END;
RETURN(VRESULTADO);
END CADENA_REENSAMBLADA;
Invocar a la función sería algo así como VCADENA := CADENA_REENSAMBLADA(<el_id_como_number>, <el_dblink_como_string>);, ej.: VCADENA := CADENA_REENSAMBLADA(1, 'DBLINKSQLSERVER');