Export Oracle File to Delimited Text

Is there an easy way to export an Oracle table to a delimited text file. The file has 30 million records so I really cannot use ODBC. I think there is a way using a SQLPLUS script using SPOOL. I would like to preserve the records layout.

Any help is appreciated.

Thanks
xxxyyy22Asked:
Who is Participating?
 
actonwangCommented:
try this:
///////////////////


set echo off feedback off pagesize 0 linesize 0 trims on

spool dump.data

select column1,column2, ...columnn from yourtable
/

spool off
0
 
actonwangCommented:
you could use the following query first:

select column1,column2, ...columnn from yourtable where rownum < 100
/

to try to see if output is ok for you.

acton                                                    
0
 
actonwangCommented:
Here is a very good link to automate this process:

http://asktom.oracle.com/~tkyte/flat/

download unloader.zip and execute as in article.

Acton
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
actonwangCommented:
after you unzip the files, look at flat.exe or flat dependng on your os.
0
 
fmonroyCommented:
an easy way is:

select TO_CHAR(FIELD1)||','||TO_CHAR(FIELD2)||','||TO_CHAR...
from your table;

remember the pagesize setting and the spool thing before runnig the query.
0
 
schwertnerCommented:
Technologically it is easy to get the output in CSV format.
The problem is the amount of the records.
Every OS has limitation on the file size (Linux, Unix - 32 bits - the file should
be not greater then 2 GB).
If it is very large file try to divide it in many small files.
0
 
DLyallCommented:
I agree with schwertner, the number of records you are trying to export to a single file will make that file difficult to create and process.  Also an application like Excel, which is commonly used to view flat file delimited data has a limit of 65536 rows on a worksheet.  How were you intending to use the delimited flat file once it is created? It might be better to split the data into a number of smaller files.
0
 
DLyallCommented:
:xxxyyy22

Do you require further assistance?

If not then,

If your question has been answered by one of the above comments could you award points and close the question please.

If none of the comments have been helpful please ask for a refund and have the question closed.

Thanks

DLyall
0
 
xxxyyy22Author Commented:
I have found a solution on the internet but not in Experts_Exchange.

I would like the question closed.

Thanks.

0
 
fmonroyCommented:
I do not agree, please post the answer then the admins can see if your solution is not in this thread.

FM
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.