Inicio > SQL > Leer un archivo de texto mediante una consulta de BBDD

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.

  1. Marcelo
    marzo 4, 2011 en 14:21

    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…

    • xavier.ribas
      marzo 7, 2011 en 16:40

      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!

  2. jose
    septiembre 1, 2011 en 04:25

    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?

    • xavier.ribas
      septiembre 1, 2011 en 08:31

      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.

  3. jose
    septiembre 1, 2011 en 16:09

    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!!!

  4. Alvaro
    noviembre 22, 2011 en 11:38

    Hola, podrias poner un ejemplo de como tiene que ser (por dentro) el fichero de texto?

    Graicas

    • xavier.ribas
      noviembre 22, 2011 en 11:44

      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.

  5. Wilson Andres Robayo
    julio 12, 2012 en 23:22

    Wilson Andres Robayo :buenas tardes, cuanto hago la consulta para un archivo .csv me sale la primera linea con este dato “ÿþI” y las demas vacias.
    Gracias

    • Xavier Ribas
      julio 16, 2012 en 08:32

      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

  6. Fran
    septiembre 11, 2012 en 09:26

    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í?

  7. Fran
    septiembre 11, 2012 en 09:27

    Fran :
    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é..

    • Xavier Ribas
      septiembre 14, 2012 en 08:28

      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;
      /
      
  8. marvin
    agosto 22, 2014 en 18:30

    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) := vLinea;
    end loop;

    utl_file.fclose (vArchivo);

    For vIdx in tDatos.first .. tDatos.last loop
    Pipe row (tDatos(vIdx));
    end loop;

    return;
    END;
    /

  1. enero 2, 2014 en 09:46

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 127 seguidores

%d personas les gusta esto: