Objetivos
- Escribir y leer archivos de texto en 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.
El Paquete UTL_FILE
El paquete UTL_FILE permite leer, escribir, crear, modificar y administrar archivos de texto en el sistema de archivos del servidor donde se ejecuta Oracle. Se utiliza principalmente para intercambiar datos con archivos externos, generar reportes, exportar información y procesar cargas de datos desde archivos.
Métodos del Paquete: UTL_FILE:
Ejemplos
Creamos el directorio dir_archivos:
CREATE OR REPLACE DIRECTORY dir_archivos
AS 'C:\ORA_DIR';De igual manera creamos la carpeta en la ruta indicada C:\ORA_DIR:
Creamos la secuencia seq_archivo:
CREATE SEQUENCE seq_archivo
START WITH 1
INCREMENT BY 1;A continuación creamos el procedimiento p_crear_archivo que lee de las tablas employees, departments y locations para luego generar un archivo con las informaciones de los empleados. El bloque muestra el uso de los procesos FOPEN, PUT_LINE y FCLOSE.
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
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;
v_datos :=
'"'||rec.nombre||
'","'||rec.salario||
'","'||rec.departamento||
'","'||rec.ciudad||
'"';
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;Invocamos el procedimiento p_crear_archivo:
BEGIN
p_crear_archivo;
END;
El siguiente bloque anónimo muestra como leer los registros del anterior archivo generado. Notar el uso de GET_LINE y FGETATTR.
SET SERVEROUTPUT ON
DECLARE
v_archivo UTL_FILE.FILE_TYPE;
v_nom_arch VARCHAR2(50) DEFAULT 'DETALLE_EMPLEADOS1.CSV';
v_nom_dir VARCHAR2(50) DEFAULT 'DIR_ARCHIVOS';
v_datos VARCHAR2(500);
v_file_exists BOOLEAN;
v_file_length NUMBER;
v_block_size BINARY_INTEGER;
BEGIN
UTL_FILE.fgetattr(v_nom_dir,
v_nom_arch,
v_file_exists,
v_file_length,
v_block_size);
IF v_file_exists THEN
DBMS_OUTPUT.PUT_LINE('Archivo '||v_nom_arch||'; Bytes '||v_file_length||'; Block '||v_block_size);
v_archivo := UTL_FILE.FOPEN
(v_nom_dir,
v_nom_arch,
'R');
LOOP
BEGIN
UTL_FILE.get_line(v_archivo,v_datos);
DBMS_OUTPUT.PUT_LINE(v_datos);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE(v_archivo);
ELSE
DBMS_OUTPUT.PUT_LINE('Archivo '||v_nom_arch||' no encontrado!');
END IF;
END;
La siguiente imagen muestra el resultado de tratar de encontrar un archivo no existente en la ruta especificada.
El siguiente nombre muestra como renombrar un archivo dado con el uso de FRENAME.
SET SERVEROUTPUT ON
DECLARE
v_nom_arch VARCHAR2(50) DEFAULT 'DETALLE_EMPLEADOS1.CSV';
v_nom_dir VARCHAR2(50) DEFAULT 'DIR_ARCHIVOS';
BEGIN
UTL_FILE.frename(v_nom_dir,
v_nom_arch,
v_nom_dir,
'Nuevo_Nombre_Archivo.csv',
TRUE);
END;
El siguiente bloque muestra como eliminar un archivo con uso de proceso FREMOVE.
SET SERVEROUTPUT ON
DECLARE
v_nom_arch VARCHAR2(50) DEFAULT 'Nuevo_Nombre_Archivo.csv';
v_nom_dir VARCHAR2(50) DEFAULT 'DIR_ARCHIVOS';
BEGIN
UTL_FILE.FREMOVE(v_nom_dir,
v_nom_arch);
END;
Conclusión
El manejo de archivos de texto con el paquete UTL_FILE amplía las capacidades de PL/SQL más allá del procesamiento tradicional en base de datos, permitiendo interactuar de forma controlada con el sistema de archivos y automatizar tareas frecuentes de integración, auditoría y generación de información.
Entre sus operaciones más relevantes, GET_LINE y PUT_LINE permiten construir flujos completos de lectura y escritura de archivos, facilitando desde la carga de datos hasta la generación de reportes y bitácoras. Por su parte, FGETATTR agrega una capa de validación al permitir verificar la existencia y características de un archivo antes de procesarlo, reduciendo errores y fortaleciendo la lógica de negocio. Complementando este ciclo, FRENAME y FREMOVE aportan capacidades de administración sobre los archivos generados, permitiendo renombrarlos, organizarlos o eliminarlos como parte de procesos automatizados.
En conjunto, estas funciones convierten a UTL_FILE en una herramienta clave para implementar flujos complejos y seguros dentro del ecosistema Oracle, donde la gestión de archivos forma parte integral de las reglas de negocio y de la integración entre sistemas.