Export oracle data in comma separated value format

luvcal · #1 (**permalink**) 04-12-02, 13:01

Is there any way to export data into a comma separated value file format in Oracle?? Thanks for any and all help.

Ruudboy · #2 (**permalink**) 04-15-02, 02:28

Hi,

You can use the tool SQL Loader for that.
For a manual on how it works look here:
http://technet.oracle.com/docs/produ.../ch03.htm#2436

fromeast · #3 (**permalink**) 04-29-02, 04:12

It can only load data into Oracle.

You can export data in text format by using Excel, TOAD etc.

rhs98 · #4 (**permalink**) 04-29-02, 05:01

just do a sql script!

Code:

set termout off
set hea off
set pagesize 0

spool c:\whatever.csv

select a.a||','||a.b||','||a.c
from a
where a.a="whatever";

spool off

should be quite fast. remeber the ; at the end of the sql select

Ruudboy · #5 (**permalink**) 04-29-02, 09:24

Quote:

Originally posted by fromeast
It can only load data into Oracle.

Ahh.. duh.. should read better, before answering

Thought it was for reading comma seperated files. Sorry

Yes, the way rhs98 mentiones is the best I guess.
Ruud

kerlion · #6 (**permalink**) 05-08-02, 00:44

PL/SQL Developer can do it ease

rhs98 · #7 (**permalink**) 05-08-02, 16:31

oh yeah, remeber if your using this more than once, to use bind variables in the sql; as it can/does speed things up alot

rayvid123 · #8 (**permalink**) 05-11-02, 13:15

I would suggest following ways to perform export data from Oracle:

1> Use serveroutput settings and DBMS_OUTPUT.PUT_LINE
Disadvantage: Every line is buffered to a buffer. Minimum buffer is of 2000 bytes and maximum is of 1,000,000 bytes. Performance is screwed up and has limitation. Besides I think DBMS_OUTPUT.PUT_LINE should be used to find out logical errors, but if this is a temporary project, go for it. DBMS_OUTPUT is not designed to be a report writer. It is meant to be used for debugging. It is not really powerful enough for much more than this. The procedures in this package provide much the same functionality as the printf() function in C.

2> Use Select from sqlplus:
If you want to create a production job using select, seek some other profession or retire yourself from database field. Again if this is a temporary project, rather than spending your organizations’ valuable time in coding “C”, or sqlJ, go with this solution.

3> Best and simple way use Pro*C. U can manipulate, twist, grind do whatever you want to using "C".

4> Use SqlJ

There are many ways for doing this. It depends on to what level your supervisor is stupid/not-technical. Usually in a non-professional organization the supervisor would suggest you to do in a very simple way not really knowing the correct, appropriate ways of doing and ignoring the long-term consequences. If you work for a professional technical supervisor, he/she will advise any one of the above or may be some other ways that I haven’t listed above.

Hope this answers your question. Feel free to comment to my above discussion.

john_miller · #9 (**permalink**) 01-13-05, 16:45

Quote:

Originally Posted by luvcal

Is there any way to export data into a comma separated value file format in Oracle?? Thanks for any and all help.

If your tables in Oracle are not big or you need to perform oracle data extract in test environment, then all suggestions above should work.

If you large tables (millions of rows or even terabytes of data), blob type, chained rows,.. etc, you may want consider third party tool. Especially if you need to unload data quickly from production server with no adding cpu overhead. I would suggest wisdomforce Fastreader www.wisdomforce.com .

Fastreader is really fast, support all features that production DBA need and also generates loaders for not just Oracle, but also DB2, MySQL, SQL Server. This is very convinient for automating migrate process.