Leer un archivo de texto mediante una consulta de BBDD
En Oracle existen las tablas externas que nos permiten vincular archivos de texto a tablas de base de datos. Este sistema es útil en numerosos casos, pero tiene el inconveniente que hay que declarar la estructura del archivo (columnas, separador, nulos…), tarea a menudo laboriosa. A veces sólo nos interesa poder consultar los datos de un archivo de forma ocasional. Vamos a detallar un método que nos permitirá leer mediante una consulta el contenido de un archivo sin necesidad de declarar tablas externas.
La solución propuesta consiste en la creación de una función que lee el archivo y devuelve el contenido de éste en un array. Para conseguir mejores resultados en el caso de archivos grandes, usaremos el método de las “pipelined table functions”, que nos permitirá ir devolviendo las líneas a medida que son leídas desde el archivo.
El primer paso será crear un tipo array de varchar2.
CREATE TYPE Ttb_archivo_texto AS TABLE OF VARCHAR2(4000);
A continuación creamos la función que lee el archivo. La función recibirá por parámetro el directorio de lectura y el nombre del archivo a leer. Devolverá el contenido del archivo en un array del tipo que acabamos de crear.
CREATE FUNCTION LEER_ARCHIVO (P_DIRECTORIO VARCHAR2, P_ARCHIVO VARCHAR2 ) RETURN Ttb_archivo_texto PIPELINED AS vArchivo utl_file.file_type; vLinea varchar2(4000); BEGIN vArchivo := utl_File.fopen (P_DIRECTORIO, P_ARCHIVO, 'R'); -- Leemos cada una de las líneas del archivo y la retornamos Loop Begin utl_file.get_line (vArchivo, vLinea); exception when NO_DATA_FOUND then exit; end; Pipe row (vLinea); end loop; utl_file.fclose (vArchivo); return; END; /
Ya podemos usar la función en una consulta. Para ello, vamos a llamarla en la cláusula FROM utilizando la función pl/sql “table”:
Select COLUMN_VALUE
from table (LEER_ARCHIVO ('OUT_SALDSV', 'test.txt'));
Nota: COLUMN_VALUE es el nombre que recibe por defecto la columna devuelta por la función de tabla.
Deja un comentario
Nuestra red social |
|
|
Hola:
estoy usando la funcion para leer archivos grandes, usando pipelined table functions, pero e arroja el siguiente error:
PLS-00653: Las funciones aggregate/table no estan permitidas en el ámbito de PL/SQL. te agradecería e pudieras ayudar con esto…
Hola Marcelo,
¿me podrías indicar en que momento te de el error? ¿Estas usando el código que hay en el post o bien has introducido algún cambio? Si es así, ¿podrías mostrarlo? ¿Estás llamando a la función de modo autónomo en un bloque PL/SQL? Las funciones PIPELINED sólo pueden ser llamadas dentro de un SQL. Es decir, si deseas usarla en un procedure por ejemplo, deberías realizar un cursor del estilo “Select NOMBRE_FUNCION from dual;”.
Espero tu respuesta.
Saludos!
Excelente me funcino a la perfecccion ahora tengo una pregunta si lo quiero usar en un bloque como haría? cuando lo coloco en un select LEER_ARCHIVO (‘DIRECTORIO’, ‘test.txt’) from dual me da como resutado (DATASET) que puedo hacer?
Hola jose,
para poder trabajar en un bloque, debes colocar la select dentro de un cursor:
DECLARE CURSOR cFic IS SELECT COLUMN_VALUE FROM TABLE (LEER_ARCHIVO ('DIRECTORIO', 'test.txt')); vLinea varchar2(1024); BEGIN Open cFic; Fetch cFic into vLinea; While cFic%FOUND loop -- vLinea va tomando el valor de cada una de las líneas del fichero -- Aquí puedes trabajar con el contenido de la lína Fetch cFic into vLinea; end loop; Close cFic; END; /Espero que con esto haya respondido a tu pregunta.
Saludos.
Si gracias funciona perfecto yo lo estaba haciendo asi:
for reg in cFic loop
— Aquí puedes trabajar con el contenido de la lína
end loop;
pero no funciona!!
muchas gracias amigo!!!
Hola, podrias poner un ejemplo de como tiene que ser (por dentro) el fichero de texto?
Graicas
Hola Álvaro,
el fichero de texto no tiene que tener ninguna estrucutra especial. Únicamente con que sea un fichero de texto plano es suficiente. Sólo puedes encontrar problemas si el tamaño de las línias supera el tamaño con el que se definió el tipo que las almacena.
Saludos.
Hola Wilson,
es difícil darte una explicación sin conocer como has realizado la implementación y sin saber como son los datos del archivo csv que quieres leer. Puede ser que el archivo sea binario, en cuyo caso sería normal que no vieras el contenido esperado.
Saludos
De gran ayuda. Solo un tema.
Si ejecuto la función sobre un fichero de texto al cabo de un tiempo me acaba saltando el error:
en la linea:
vArchivo := utl_File.fopen (P_DIRECTORIO, P_ARCHIVO, ‘R’);
SYS.UTL_FILE operación de archivo no válida.
¿Alguna idea de porque esto puede seer así?
al cabo de un tiempo quiero decir si ejecuto la select:
Select COLUMN_VALUE
2
from table (LEER_ARCHIVO (‘OUT_SALDSV’, ‘test.txt’));
varias veces seguidas en la misma sesion. Es como si se llenase un buffer o algo. Nosé..
Hola Fran,
el problema podría deberse a que has llegado a tener abiertos el número máximo de archivos posibles. Es decir, si el archivo “test.txt” es muy grande y no lo llegas a visualizar completamente, la función no llega a cerrar el archivo. Si vas repitiendo la operación, al final van quedando archivos abiertos. Para solventar este problema, se podría realizar un cambio en la función que consistiría en cargar todo el contenido del archivo de una sola vez en una tabla PL/SQL, y ir devolviendo las filas de esta con el PIPE ROW. De este modo, te asegurarías que siempre se cierra el archivo.
Sería algo así:
CREATE FUNCTION LEER_ARCHIVO (P_DIRECTORIO VARCHAR2, P_ARCHIVO VARCHAR2 ) RETURN Ttb_archivo_texto PIPELINED AS vArchivo utl_file.file_type; vLinea varchar2(4000); tDatos Ttb_archivo_texto; BEGIN vArchivo := utl_File.fopen (P_DIRECTORIO, P_ARCHIVO, 'R'); -- Leemos cada una de las líneas del archivo y la retornamos Loop Begin utl_file.get_line (vArchivo, vLinea); exception when NO_DATA_FOUND then exit; end; tDatos.extend(1); tDatos(tDatos.last) := vLine; end loop; utl_file.fclose (vArchivo); For vIdx in tDatos.first .. tDatos.last loop Pipe row (tDatos(vIdx)); end loop; return; END; /