Escribir resultado de una select en un fichero

[roscar]

Alguien puede decirme como puedo en pl sql escribir el resultado de una select en un fcihero?Se que es con utl_file pero no se como

Gracias

[matanga]

Hola,

Este es un ejemplo muy basico de como utilizar el paquete UTL_FILE

Tengo la siguiente tabla de ejemplo.

Código:

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NOMBRE                                             VARCHAR2(30)
 NACIMIENTO                                         DATE

SQL> select * from t1;

        ID NOMBRE                         NACIMIENT
---------- ------------------------------ ---------
         1 nombre 1                       01-JAN-70
         2 nombre 2                       28-DEC-71
         3 nombre 3                       08-DEC-75
         4 nombre 4                       06-JAN-78
         5 nombre 5                       24-DEC-79
         6 nombre 6                       31-DEC-89

6 rows selected.

Para empezar, creas un objeto DIRECTORY, en esta ubicacion se van a generar el o los ficheros.

Código:

SQL> create directory utl_data as '/home/oracle/scripts/data';

Directory created.

Despues creas un procedimiento que recorre una tabla y por cada registro escribe una linea en el fichero ASCII.

Código:

SQL> create or replace procedure p1 is
  2  
  3    line varchar2(500);
  4    file utl_file.file_type;
  5  
  6  begin
  7  
  8    --Abres el fichero t1.dat en el directorio UTL_DATA,
  9    --la w sigfinica en modo escritura y que lo cree si no existe.
 10    file := utl_file.fopen('UTL_DATA','t1.dat','w');
 11  
 12  
 13    --Defines un cursor for loop que recorre la tabla t1
 14    for rec in (select id, nombre, nacimiento from t1) loop
 15  
 16      --Generas la linea, agregando si es necesario, un delimitador de campo,
 17      --en este ejemplo elegi el |
 18      line := to_char(rec.id) || '|' || rec.nombre || '|' || to_char(rec.nacimiento,'dd/mm/yyyy');
 19  
 20      --Escribes la linea en el fichero
 21      utl_file.put_line(file,line);
 22  
 23    end loop;
 24  
 25    --Cierras el handler al fichero
 26    utl_file.fclose(file);
 27  
 28    exception
 29      when others then
 30        dbms_output.put_line(sqlerrm);
 31  
 32  end p1;
 33  /

Procedure created.

Ejecutas..

Código:

SQL> exec p1;

PL/SQL procedure successfully completed.

Y listo, ahora solo queda ver el contenido del fichero

Código:

oracle@buo:~/scripts/data> more t1.dat 
1|nombre 1|01/01/1970
2|nombre 2|28/12/1971
3|nombre 3|08/12/1975
4|nombre 4|06/01/1978
5|nombre 5|24/12/1979
6|nombre 6|31/12/1989

Este ejemplo solo soporta tipos de datos NUMBER, VARCHAR2, CHAR y DATE, es decir, no va a funcionar si tienes, por ejemplo, campos LONG o LOBs.

Tambien tienes la opcion de hacer un spool con el SQL*Plus.

Código:

SQL> set head off
SQL> set feed off
SQL> set echo off
SQL> set pages 0
SQL> spool t2.dat
SQL> select id || '|' || nombre || '|' || to_char(nacimiento,'dd/mm/yyyy') from t1;
1|nombre 1|01/01/1970
2|nombre 2|28/12/1971
3|nombre 3|08/12/1975
4|nombre 4|06/01/1978
5|nombre 5|24/12/1979
6|nombre 6|31/12/1989
SQL> spool off

Pero despues tendras que eliminar algunas lineas del fichero

Código:

oracle@buo:~/scripts/data> more t2.dat 
SQL> select id || '|' || nombre || '|' || to_char(nacimiento,'dd/mm/yyyy') from t1;
1|nombre 1|01/01/1970                                                           
2|nombre 2|28/12/1971                                                           
3|nombre 3|08/12/1975                                                           
4|nombre 4|06/01/1978                                                           
5|nombre 5|24/12/1979                                                           
6|nombre 6|31/12/1989                                                           
SQL> spool off

Saludos

[roscar]

Muchas gracias, así me entero de como se usa el UTL_FILE. Aunque al final lo hice con el spool, es mucho más rápido y para lo que necesitaba me servía. Gracias de todas maneras Matanga.

[kikolice]

"al final lo hice con el spool"

no pude evitar la sonrisa, "matanga" dandonos una extensa guia de como utilizar utl_file y el usuario con otra de las suyas, usuarios al fin y al cabo

[matanga]

Hola,

Y si, un poco de risa da

Al menos ya esta escrito, si alguien mas pregunta por UTL_FILE ya se donde hacer referencia.

Saludos