question

OraLearner avatar image
OraLearner asked

Can we write data from a table to a file?

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.

oracle
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Andrew Mobbs avatar image
Andrew Mobbs answered

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.]
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

its really simple. Thank you.
0 Likes 0 ·
prodlife avatar image
prodlife answered

Tom Kyte collected a bunch of ways to do that: http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteflat.html

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I verified the link. The links are good. I am suprised of seeing so many ways to do this. Thanks for the link!
0 Likes 0 ·
KillerDBA avatar image
KillerDBA answered

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?

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Victor Saavedra avatar image
Victor Saavedra answered

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.]

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.