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.
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 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?
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.]
No one has followed this question yet.