Exportar fácilmente datos de Oracle a un fichero plano

By Carlos on 9 February, 2010 - 14:25
Plataforma de datos

Una manera muy simple de exportar datos de una consulta, tabla, etc. de una base de datos oracle a un fichero plano es utilizar el comando SPOOL de SQLPlus. De esta manera no es necesario depender de herramientas visuales, que no siempre están disponibles, o no siempre funcionan como queremos.

Además, se pueden utilizar las funciones de formato de Oracle en la misma sentencia SELECT para que los datos se generen ya en el formato que necesitemos.

Ejemplo de exportación de datos a un fichero plano desde SQLPlus

Si, por ejemplo, queremos recuperar algunos datos de todos los registros de una tabla de clientes ordenados por fecha de alta, sólo hay que abrir una sesión de SQLPlus y ejecutar esta serie de comandos:

SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL C:\datos_de_clientes.txt
SQL> SELECT 'Cliente ' || CLI_NOMBRE || ', ' || CLI_NIF || '. Fecha alta: ' || TO_CHAR(CLI_FECHAALTA,'YYYY-MM-DD') 
     FROM TABLA_CLIENTES
     ORDER BY CLI_FECHAALTA DESC;
SQL> SPOOL OFF;
SQL> SET FEEDBACK ON
SQL> SET HEADING ON

Las primeras líneas ocultan las cabeceras que contendrían el nombre de los campos, y no nos interesan porque nosotros sólo queremos los datos. Spool dirige la salida de datos hacia el fichero 'datos_de_clientes.txt' de la unidad C de nuestra máquina local.

Esta técnica también se puede emplear para menesteres más sofisticados como la construcción dinámica de scripts a partir de los metadatos que tenemos disponibles en nuestra BD.

🎓 ¿Quieres dominar Oracle SQL?

Si estás aprendiendo Oracle y quieres ir más allá de los scripts básicos, este curso completo en español te lleva desde cero hasta la preparación de la certificación oficial 1Z0-071.

Ver curso de Oracle SQL en Udemy →

Enlace de afiliado • Dataprix puede recibir una comisión

Libros de Administración Oracle (DBA) y PL/SQL

¿Quieres profundizar más en PL/SQL de Oracle o en administración de bases de datos Oracle? Puedes hacerlo consultando alguno de estos libros de Oracle.

Los libros que ves a continuación son una selección de los que a mi me parecen más interesantes para aprender administración y desarrollo PL/SQL, teniendo en cuenta precio y temática, espero que te puedan ser de utilidad:

  • eBooks de Oracle gratuítos para la versión Kindle, o muy baratos (menos de 4€):

 

  • Libros recomendados de Oracle

 

ficheros
DBA

Submitted by Juan Manuel (not verified) on 26 December, 2013 - 20:34

Hola, existe algun modo de exportar a un archivo csv? Gracias

Submitted by Francisco (not verified) on 4 February, 2014 - 21:29

Hola
puedo con el commando spool ir agregando datos a un archivo .xls o .csv?

Gracias de antemano

Submitted by Kimbies (not verified) on 18 September, 2014 - 22:20

Gracias por las respuestas, pero cómo lo haría si necesito ejecutar un Stored Procedure, el cual retorna la data que necesito en un REF CURSOR?

Submitted by Frank (not verified) on 3 October, 2014 - 22:25

Favor me pueden ayudar a solucionar este error? sqlplus

"result of string concatenation is too long"

Encontre esto pero no salen los resgistros completos:
SET LONG 100000;
SPOOL test_clob.txt
SELECT to_clob(lpad('A',4000,'A')) <- Aca inclui la query con un laargo de 4000
||'B'
||to_clob(lpad('C',4000,'C')) <- Aca inclui la query con un largo de 1000
FROM dual;
SPOOL OFF;

Submitted by Paula (not verified) on 11 November, 2014 - 17:33

Hola, su ayuda por favor, tengo TOAD y quiero exportar a un archivo csv, dado que cuando exporto a excel, me separa en varias hojas cuando sobrepasa los 65000 registros.

Gracias por sus comentarios!!

Submitted by Carlos on 11 November, 2014 - 23:17

Puedes hacerlo con SQLPlus con las indicaciones de este post, no hace falta que utilices TOAD. Lo único que tienes que cambiar es la SELECT e ir concatenando las comillas, comas, etc. para que te vaya montando los campos en formato csv.

Y si quieres probar una manera más fácil, en esta web he encontrado un truquillo que si funciona bien es de lo más sencillo. Parece que hay hints para forzar el formato de salida de las queries, así que para sacar los datos de una tabla en formato csv, con el mismo método de SPOOL del post sólo tendrías que hacer algo así como:

SELECT /*csv*/ * from tabla;

 

Submitted by Abril (not verified) on 23 November, 2014 - 05:00

Exite alguna manera de guardar la salida de una consulta en un archivo, pero sin que la salida de la consulta se vea por pantalla.

Gracias.

Submitted by Dataprix on 24 November, 2014 - 23:48

 Para que no se muestre por pantalla, puedes ejecutar la consulta de Oracle en background, incluyendo el Spool para hacer la exportación de los datos al fichero de salida.

Tienes que crear un fichero con extensión .sql con las mismas lineas que se indican en el post:

SET HEADING OFF
SET FEEDBACK OFF
SPOOL C:\datos_de_clientes.txt
SELECT 'Cliente ' || CLI_NOMBRE || ', ' || CLI_NIF || '. Fecha alta: ' || TO_CHAR(CLI_FECHAALTA,'YYYY-MM-DD')
FROM TABLA_CLIENTES
ORDER BY CLI_FECHAALTA DESC;
SPOOL OFF;
SET FEEDBACK ON
SET HEADING ON

Y después, desde linea de comandos ejecutas con SQLPlus el fichero en background incluyendo un & al final del comando. Algo así:

> sqlplus usuario/password@basededatos @QueryConSpool.sql &

 

Submitted by carlos sepulveda (not verified) on 3 December, 2014 - 23:07

hola buena tarde

pido su ayuda urgentemente, por cuanto el número de cédula de un empleado para hacer una transferencia no aparece al momento de exportar el archivo desde oracle SQL Developent para convertirlo en archivo plano. agradezco enormemente sus respuestas

Submitted by Jose Oracle (not verified) on 10 December, 2014 - 12:18

Si es un campo normal y no aparece es porque no está incluído en la SELECT.
¿Si ejecutas la select para verla por pantalla te aparecen los datos del campo?

Submitted by Rodigilber Gonzalez (not verified) on 11 May, 2015 - 15:50

Hola la informacion se me hace muy util pero me gustaria saber como hacer lo contrario. lo que pasa es que yo necesito listar una serie de archivos que tengo en mi directorio local pero no encuentro la manera de acceder al sistema de archivos local, toda la informacion que he encontrado usa algo asi " SYS.DBMS_BACKUP_RESTORE.searchFiles(dir, ns);" pero no es valido para mi sistema de archivos locales.

Submitted by Giancarlo Casas (not verified) on 11 June, 2015 - 22:15

Existe añguna forma de almacenar el resultado del un spool es un campo de una tabla sin generar el archivo fisicamente??

Submitted by Edward R (not verified) on 28 August, 2015 - 19:15

si quisiera usar ese procedimiento en linux desde shell funciona de la misma manera el spool en linux?

 

gracias.

Submitted by Carlos on 31 August, 2015 - 12:34

Puedes utilizar el spool igualmente en Linux, una vez dentro de SQLPlus los comandos SQL y de Spool son los mismos para cualquier sistema.

Submitted by carlos.segura (not verified) on 17 September, 2015 - 04:17

Hola amigo tengo una duda cuando exporto mi consulta a excel me coloca toda la informacion en una columna, como puedo hacer para que me lo separe?. de antemano gracias 

Submitted by Carlos on 17 September, 2015 - 12:25

Hola Carlos

Puedes modificar la consulta para que te genere los datos en formato CSV, es decir, los datos de cada columna separados por comas y los strings entre comillas.
De esta manera después puedes importar el fichero en Excel indicando que es formato CSV (ponle de extensión .csv), y Excel ya te dejará cada columna en su sitio.

Submitted by carlos.segura (not verified) on 21 September, 2015 - 04:27

Hola gracias por tu respuesta, mira esta es mi consulta

SET SPACE 0 SET LINESIZE 75 SET PAGESIZE 0 SET HEADING OFF SET MARKUP HTML OFF SPOOL OFF SET COLSEP " " SPOOL archivo1.csv select "EMAIL_ADDR" ||','|| "NOMBRE" ||','|| "SPECIALITY" from iof_cam; SPOOL OFF;

pero cuando abro el archivo me sigue saliendo en una sola columna, no me podrias decir cual es mi error o que es lo que tengo que modificar? 

gracias

Submitted by Carlos on 21 September, 2015 - 11:59

Las comillas las has puesto directamente con el nombre del campo, y tienes que ponerlas como un string, si te miras el fichero de resultado verás que no te escribe ninguna comilla doble. Para que te incluya bien las comillas deberías cambiar la select por algo así:

select '"'|| EMAIL_ADDR ||'","'|| NOMBRE ||'","'|| SPECIALITY ||'"' from iof_cam;

Así los datos de cada campo deberían salirte con comilla doble, en formato csv, y Excel tendría que dejar cada campo en una columna al importar el fichero .csv

Saludos,

Submitted by Hilario (not verified) on 29 September, 2015 - 15:27

Hola carlos

soy nuevo en esto de sql y ya he hecho varios query pero necesito ponerle encabezados a los reportes generados por estos query y no he podido hacerlo, hay alguna forma de poder hacerlo?

Muchas Gracias,

Saludos.

Submitted by Carlos on 8 October, 2015 - 19:09

Hola Hilario

Puedes incluir los encabezados en la misma sentencia select utilizando un union. El SQL sería algo parecido a esto:

SELECT 'Cliente, NIF, Fecha alta' FROM dual
UNION
SELECT 'Cliente '|| CLI_NOMBRE ||','|| CLI_NIF ||'. Fecha alta: '|| TO_CHAR(CLI_FECHAALTA,'YYYY-MM-DD')
FROM TABLA_CLIENTES
ORDER BY CLI_FECHAALTA DESC;

Submitted by Anonimo (not verified) on 30 September, 2015 - 17:01

Hola, como eliminar el mensaje "Input truncated to 9 characters" que se crea al final del archivo que se exporta en el spool.

Submitted by Sergiosalgadod (not verified) on 19 October, 2015 - 22:52

Hola que tal mi problema es el siguiente Mi problema es el siguiente necesito extraer las imágenes en formato jpg que están en una tabla de oracle. espero me puedas apoyar saludos

Submitted by PericoPalotes (not verified) on 1 February, 2016 - 16:14

Buenas tardes,

necesito realizar una descarga a un fichero plano de una tabla de Oracle de unos 36 millones de registros. he decidio realizarlo mediante un archivo sql donde ingreso la select de la tabla, y cuyo archivo sql es llamado desde el script de unix. Mi pregunta es la siguiente ¿es viable este volumen de descarga con el sqlplus?  

 

Muchas gracias de antemano.

Submitted by Toledo (not verified) on 15 April, 2016 - 00:30

Esa cantidad de datos ningun manejador te lo va a permitir exportar sin sufrirle un poco... Aún MySQL en modo ZIP tendras que partirle la tarea.

Necesitas hacer una exportación parcial digamos de millón de registros en archivos separados... entre BDs Oracle con el DataPump lo haces rápido y te compacta el exportado...

 

Havia un archivo plano, las alternativas son que en el export le pongas una condición para reducir el conjunto de registros a exportar... o bien ve filtrando la tabla a una temporal y luego ve exportando esa temporal limitando el número de registros...

 

Recuerda que hay limitantes en algunos filesystems o medios, por si vas a enviarlo por USB, red o DVDs, dependiendo de ciertas condiciones y el tamaño de cada registro, 36millones de registros pueden sobrecargarte algunas cosas (procesador, disco, red, etc).

 

Suerte!

Submitted by Lopez Flynn (not verified) on 25 August, 2016 - 12:41

Hola,

Realice un export de la base de datos A pero al intentar importarlos a la base B, se obtiene el error indicando que la estructura de la tabla destino no es igual a la tabla origen.

Mi duda es que si existe la manera de importar los datos ignorando las columnas agregadas en la tabla destino.

 

Gracias

 

Saludos,

Submitted by Carlos on 26 August, 2016 - 09:24

Lo que puedes hacer es simplemente incluir en la select del export los nombres de los campos 'extra' de la tabla destino, dejando la parte de datos a nulo o al valor por defecto que tengan estos campos.

Saludos,

Submitted by Jose Goicochea (not verified) on 28 March, 2018 - 00:37

Estimado que tal: Me ha servido mucho la informacion . Tengo una consulta como podria hacer para ejecutar periodicamente este comando spool en un job o como podria colocarlo dentro de un procedure ya que lo he intentado pero sin exito Gracias de antemano Saludos