A menudo, al resolver problemas de integración de sistemas, es necesario presentar una cierta cantidad de datos en un formato u otro. Al mismo tiempo, cualquiera puede ser un consumidor de datos, pero la fuente es casi siempre una base de datos corporativa. Por ejemplo, un fabricante puede exigir al proveedor que informe periódicamente sobre el movimiento de sus productos en formato XLSX o XML, etc.
Existen muchas herramientas para convertir datos en varios formatos, y la posibilidad de su uso depende de la pila tecnológica adoptada en la empresa y la arquitectura del software. Al mismo tiempo, siempre desea que la cadena que consta de varias bibliotecas, marcos y capas del sistema utilizadas para convertir los datos de origen sea lo más breve posible. Esto reduciría el tiempo dedicado al desarrollo de la solución y aumentaría su eficiencia productiva.
Si asumimos que, de hecho, la consulta SQL está en la raíz del proceso de selección de datos, entonces idealmente la cadena de transformaciones sería deseable para ver esto:
donde
- datos de origen,
- Consulta SQL para recuperar datos,
- una función que convierte la selección al formato deseado,
- datos en el formato requerido.
Para Oracle PL / SQL, hay una serie de paquetes integrados y de terceros que implementan esta funcionalidad. Estos son DBMS_XMLGEN, DBMS_XMLQUERY, AS_XLSX, PL / JSON y otros.
Sin embargo, cuando surgió la pregunta sobre la conversión de datos al formato CSV, por alguna razón no había soluciones preparadas. Tuve que hacerlo yo mismo, luego se mostrará cómo.
Declaración del problema.
Cree una herramienta (paquete PL / SQL) que reciba una consulta SELECT arbitraria como una cadena o como una variable de cursor en la entrada, y devuelva un objeto del tipo CLOB encapsulando datos en formato CSV en la salida. En caso de cualquier error, se debe devolver NULL. No es necesario representar el formato CSV en sí mismo: son cadenas cuyos elementos están separados por algún carácter, con mayor frecuencia ";", pero en el caso general un carácter arbitrario puede actuar como un separador. Suponga que los caracteres 0x0D + 0x0A se usan para separar las cadenas. La primera línea en el archivo CSV suele ser el encabezado y define los nombres de las columnas.
Definir la interfaz del paquete.
CREATE OR REPLACE PACKAGE pp_csv AS PROCEDURE query2sheet( stmt IN VARCHAR2, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); PROCEDURE query2sheet( ref_cursor IN OUT SYS_REFCURSOR, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); END;
Hay dos procedimientos sobrecargados, la diferencia entre ellos es que uno de ellos recibe la solicitud como una cadena y el otro como un enlace al cursor. El segundo parámetro es la salida; este es el resultado deseado en el objeto CLOB. Finalmente, el tercer parámetro es el separador CSV.
En la implementación de estos procedimientos, el paquete DBMS_SQL incorporado nos ayudará, lo que nos permite trabajar con cursores dinámicos cuando no se sabe de antemano (en la etapa de compilación) exactamente cuántas columnas están involucradas en la selección.
Las características DBMS_SQL le permiten analizar y ejecutar dinámicamente consultas arbitrarias. Para un procedimiento que acepta una solicitud como una cadena, esto sucede así:
AS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
Para un procedimiento que toma una variable de cursor, todo es más simple: a partir de la 11ª versión de Oracle, la conversión "variable de cursor → número de cursor SQL" está disponible.
La función DBMS_SQL.TO_CURSOR_NUMBER convierte la variable REFCURSOR (de tipo fuerte o débil) en el número del cursor SQL, que luego puede pasarse a las rutinas DBMS_SQL. En este caso, la variable del cursor debe estar abierta antes de pasarla a la función DBMS_SQL.TO_CURSOR_NUMBER.
cur PLS_INTEGER := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);
Por lo tanto, ambas versiones de la llamada se redujeron a obtener el
número del cursor dinámico y el conjunto de datos asociado. El siguiente paso es obtener información sobre las columnas de este conjunto y extraer los datos en sí.
El paquete DMBS_SQL le permite describir las columnas de un cursor dinámico, devolviendo información sobre cada columna en una matriz asociativa de registros.
Para hacer esto, debe declarar la colección PL / SQL basada en el tipo de colección DBMS_SQL.DESC_TAB (o DESC_TAB2 si la consulta puede devolver nombres de columna que tengan más de 30 caracteres). Después de eso, puede usar métodos de recopilación para iterar sobre la tabla y recuperar la información del cursor.
AS cols DBMS_SQL.DESC_TAB2; ncols NUMBER;
A continuación, el paquete DBMS_SQL debe ser informado del tipo de cada columna seleccionada utilizando la consulta dinámica. Esto se hace llamando a DEFINE_COLUMN.
FOR i IN 1 .. ncols LOOP DBMS_SQL.DEFINE_COLUMN(cur, i, col_val_chr, 32767); END LOOP;
En el segundo argumento DEFINE_COLUMN, se pasa un número: la posición secuencial de la columna en la lista. El tercer argumento establece el tipo de datos de la columna del cursor. Pasa una expresión del tipo apropiado. En otras palabras, DBMS_SQL.DEFINE_COLUMN no se pasa una cadena con un nombre de tipo (por ejemplo, "VARCHAR2"), sino una variable definida con el tipo VARCHAR2.
Al definir una columna de tipo de carácter en el cuarto argumento, debe especificar la longitud máxima de los valores que se pueden cargar en el cursor.
Las operaciones preparatorias se han completado, ahora puede crear una línea de encabezado y comenzar a extraer datos.
AS cap CLOB;
Los datos se recuperan línea por línea utilizando DBMS_SQL.FETCH_ROWS y llamadas posteriores a DBMS_SQL.COLUMN_VALUE para obtener los valores de las columnas individuales.
AS bod CLOB;
Entonces solo queda recolectar el CSV resultante
DBMS_LOB.APPEND(sheet, cap); DBMS_LOB.APPEND(sheet, c_line_break); DBMS_LOB.APPEND(sheet, bod);
Manejar errores
EXCEPTION WHEN OTHERS THEN sheet := NULL;
Y cierra el cursor
DBMS_SQL.CLOSE_CURSOR(cur);
Opciones de uso del paquete
DECLARE csv CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet('SELECT empcode, fio FROM employee WHERE ROWNUM < 10', csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;
DECLARE csv CLOB; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT empcode, fio FROM employee WHERE ROWNUM < 10; DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet(cur, csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;
Eso, de hecho, es todo, el
código fuente está adjunto .
El libro ayudó en el desarrollo.
Feuerstein S., llegó B. - Oracle PL / SQL. Para profesionales