Manejando Archivos de Texto con PL/SQL (UTL_FILE)

Objetivos:
• Generar/Leer archivos de texto con PL/SQL.
• Conocer los métodos y funciones del paquete UTL_FILE.
• Crear reportes directamente de programas de PL/SQL.
• Ver ejemplos de uso práctico.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

____________________________________________________________________________________
Paquete UTL_FILE.

El paquete UTL_FILE permite leer y generar archivos de texto desde programas de PL/SQL. Hay que tener en cuenta que para ello, dichos archivos y las rutas donde se encuentran deben ser accesibles desde el servidor en el cual se ejecuta la instancia de Base de Datos. La lectura/escritura de archivos (File I/O) es una funcionalidad que se introdujo con PL/SQL Release 2.3 (Oracle 7.3 u Oracle 8.0) y es compatible con versiones posteriores. Luego de esta versión, es posible cargar datos de archivos directamente en Tablas de la Base de Datos mientras se aplica toda la potencia y flexibilidad de programación PL/SQL. Puede generar informes directamente desde PL/SQL sin tener que preocuparse acerca de las restricciones de buffer máximo de DBMS_OUTPUT.^[1]

Métodos del Paquete: UTL_FILE.
Nombre	Descripción
FCLOSE	Cierra el archivo especificado.
FCLOSE_ALL	Cierra todos los archivos abiertos.
FFLUSH	Vacía todos los datos de la memoria buffer de UTL_FILE.
FOPEN	Abre el archivo especificado.
GET_LINE	Extrae la próxima línea del archivo.
IS_OPEN	Retorna TRUE si el archivo está abierto.
NEW_LINE	Inserta una nueva marca de línea al final de la línea actual de archivo.
PUT	Agrega texto al buffer.
PUT_LINE	Agrega una línea de texto al archivo.
PUTF	Agrega texto formateado en el buffer.

____________________________________________________________________________________
Ejemplos:

CREATE OR REPLACE DIRECTORY dir_archivos

  AS  'F:\DIR';

/*Debe luego crear la carpeta de manera manual en la dirección que especifique, en mi caso la cree en el disco F:/.*/

---
CREATE SEQUENCE seq_archivo
  START WITH 1
  INCREMENT BY 1;

/*En las sentencias anteriores creamos un directorio y una secuencia para usarlos en el próximo ejemplo.*/

---
CREATE OR REPLACE PROCEDURE p_crear_archivo
  IS
    v_archivo     UTL_FILE.FILE_TYPE;
    v_seq         NUMBER;
    v_datos       VARCHAR2(500);
    v_ult_dept    departments.department_id%TYPE;
    v_total_sal   NUMBER:= 0;
    v_total_emp   NUMBER:=  0;
--
    CURSOR c_emp_dept IS
        SELECT
            e.first_name||' '||e.last_name AS nombre,
            e.salary AS salario,
            d.department_id AS cod_dept,
            d.department_name AS departamento,
            l.city AS ciudad
        FROM employees e, departments d, locations l
        WHERE e.department_id = d.department_id
        AND d.location_id = l.location_id
        ORDER BY d.department_id;
BEGIN
    v_seq       :=  seq_archivo.NEXTVAL;
    v_archivo   := UTL_FILE.FOPEN
                                  (
                                    'DIR_ARCHIVOS',
                                    'DETALLE_EMPLEADOS'||v_seq||'.CSV',
                                    'W'
                                  );

    UTL_FILE.PUT_LINE(v_archivo,'Documento: Empleados por Departamento.'||CHR(10));
    UTL_FILE.PUT_LINE(v_archivo,'Fecha: ,Lugar: ');
    UTL_FILE.PUT_LINE(v_archivo,TO_CHAR(SYSDATE, 
                      'fmDD "de" MONTH "DEL" YYYY','nls_date_language=SPANISH')||', Santo Domingo. RD.'||CHR(10));
    UTL_FILE.PUT_LINE(v_archivo,'Empleado, Salario, Departamento, Ciudad');
--
    FOR rec IN c_emp_dept LOOP

/*La siguiente validación agrega una linea con algunos detalles del último departamento, esto ocurre cada vez que cambia el departamento.*/

        IF NVL(v_ult_dept, rec.cod_dept) != rec.cod_dept THEN
            UTL_FILE.PUT_LINE(v_archivo,'Cantidad Empleados: '||v_total_emp||',Total Salario: '||v_total_sal||CHR(10));
            v_total_emp := 0;
            v_total_sal := 0;
        END IF;
---A continuación la variable: v_datos se llena con los datos extraídos del Cursor: c_emp_dept 
        v_datos   :=
                    '"'||rec.nombre||
                    '","'||rec.salario||
                    '","'||rec.departamento||
                    '","'||rec.ciudad||
                    '"';

/*Las comillas dobles(") impiden que cualquier coma en la data dañe el reporte. Cada coma(,) representa una nueva columna en el archivo, por esto usamos las comillas dobles para especificar que lo encerrado en ellas son literales.*/

        v_total_emp     := v_total_emp+1;
        v_ult_dept      := rec.cod_dept;
        v_total_sal     := v_total_sal+rec.salario;
--
        UTL_FILE.PUT_LINE(v_archivo,v_datos);
--
    END LOOP;
--
    UTL_FILE.PUT_LINE(v_archivo,'Cantidad Empleados: '||v_total_emp||',Total Salario: '||v_total_sal);
--
    UTL_FILE.FCLOSE(v_archivo);
END;
/
SHOW ERROR

/*Como las funciones/procedimientos usadas en este ejemplo fueron ya explicadas anteriormente, me limitare a dar detalles, si tienen dudas puede comentar.*/

---
BEGIN
    p_crear_archivo;
END;

/*Creo este bloque anónimo para invocar el procedimiento: p_crear_archivo y así crear el Archivo.*/

---OUTPUT:

Nota: en la publicación: Leyendo archivos con UTL_FILE en ORACLE presentamos como leer un archivo previamente creado en disco usando PL/SQL.