I understood that, we can read and load data from a file to oracle database using external tables and sql loader.
Is there any way we can do this in reverse direction? i.e writing database table data into a file.
asked
Can we write data from a table to a file?
The simple way is the "spool" command in SQLPlus that just dumps the results of a query to a file:
set trimspool on
spool <filename>
SELECT * FROM <tablename>
spool off
The default extension for the file is .lst
You may want to format the output, essential is:
set trimspool on
That removes the trailing whitespace
A useful set of options to minimise SQLPLus formatting is:
SET NEWPAGE 0;
SET SPACE 0
SET LINESIZE 2000
SET PAGESIZE 0
SET ECHO OFF
SET TRIMSPOOL OFF
SET FEEDBACK OFF
SET HEADING OFF
SET VERIFY OFF
If you want the data as CSV then select it as such:
SELECT col1||','||col2||','||col3 [etc.]
Tom Kyte collected a bunch of ways to do that: http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteflat.html
I verified the link. The links are good. I am suprised of seeing so many ways to do this. Thanks for the link!
I think it depends on how you want the procedure run. If it's from your desktop/client PC, you can use SQL*Plus. If you want to export data from the server, that might be a different issue.
Our Oracle guru says there's no utility like SQL*Loader for exporting files, at least not supplied by Oracle (I had asked him about this the other day). A quick glance around suggests there are third-party tools.
If you have access to MS SQL Server, using DTS/SSIS might be the easiest way to do a server-based process. It will probably be reasonably fast. If you're extracting data from Oracle to load it into SQL Server, DTS/SSIS would be ideal.
If you have no budget but have Office or Access, I just tested writing a cruddy VB program using ODBC and on my 7 megabit DSL line, I'm getting about 12MB extracted in about 45 seconds, with no performance tricks, almost 300Kbytes/second. OLE-DB might do better. You can easily embed the necessary VB code into either tool. Some of Access' built-in tools might work better than code.
I guess, to get a good answer, it would be best if you said what kind of data, how much, how often and how it will be controlled (scheduled or user-initiated?). And are you Windows or Linux-centric?
I just want to export data from a oracle table to a text file. Thanks for your good explanation. It really make me think about something more.
If you are using CSV output, don't forget to REPLACE() the commas that may exist as part of the data: SELECT col1||','||REPLACE(col2,',',' ')||','||col3 [etc.]
