Easily export data from Oracle to flat file

Submitted by carlos on 15 February, 2010 - 12:49
Printer-friendly version

A simple way to export data from a query, table, etc.. of an oracle database to a flat file is to use the SPOOL command in SQLPlus. This would not need to rely on visual aids, which are not always available or do not always work as we want. Also you can use the Oracle format functions in the same SELECT statement that generated the data already in the format we need.

If, for example, we want to retrieve some data from all records in a table of customers sorted by date of discharge, simply open a SQLPlus session and run this series of commands: 


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

The first lines hide the headers that contain the field name, and do not concern us because we only want the data. Spool directs the output of data to the file 'datos_de_clientes.txt' on the C drive on the local machine.

This technique can also be used for necessities such as more sophisticated dynamic construction of scripts from the metadata available to us in our BD.

 
appreciation by Hareton (not verified)
Google
 
     

Who uses our public data?. Can we be really quiet?.
If you have read the newspaper El Mundo on Sunday (May 23), we find on page 10 an interesting article titled " The Psoe creates a database that covers all of Spain , where...
JPivot: 3 new tutorials
Hi, I recently created a group in the BI Open Network , dedicated to JPivot: Also in this group have created a series of tutorials: [Tutorial 01] location Graphics :...
Oracle 10g: Looking activity "extra-ordinary" in our database
How to Twitter from a procedure in Oracle PL / SQL
API Calling Twitter from stored procedures PL / SQL can send status updates to a Twitter account. Apart from the funny thing it may be, can be a very effective method to warn us...
We release the weekly Newsletter Dataprix
We have modified the email subscription system to Dataprix content. So far this subscription using the Feedburner system associated with the main RSS Dataprix to send...
Changing importance of Dataprix, Alexa
Today we have consulted our position in the Alexa ranking , and we have been pleasantly surprised to find that in the month of March we are located at or even above portals such...