• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1209
  • Last Modified:

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
0
xxxyyy22
Asked:
xxxyyy22
  • 4
  • 2
  • 2
  • +2
3 Solutions
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now