Creating/Exporting to a text File from Oracle

Posted on 2000-03-17
Last Modified: 2012-08-14
I want to know if there is a way to export tables (or the result from a Select statement) from Oracle to a text file in fast way (a table of millions of records).

I tried in the past UTL_FILE functions and PRO*C/C++ routines that writes each row after opening a cursor.

SQL Plus spool option I think it´s no good idea because I need that this file generation got to be made in automatic way (called from a Oracle Form or PL/SQL Block or PRO*C/C++)

Is there a export option for this ? or maybe a better way to make this porcess ?
Question by:NetoMan
  • 7
  • 2
  • 2
  • +4

Expert Comment

ID: 2629826
You'll need 3rd party tool. Like Platinum or BMC.

PLATINUM Fast Unload for Oracle is a high performance data export utility.

It provides full support for all Oracle7 type objects as well as partitioned tables in Oracle8, and unloads data from data files larger than 2GB.

Fast Unload exports data substantially faster than Oracle Export by directly accessing Oracle database files, completely bypassing the SQL engine and Oracle kernel.

On a multiprocessor host, Fast Unload provides even more significant increases in throughput via asynchronous I/O and faster data handling.

This data export utility is integrated with PLATINUM TSreorg to speed up the reorganization of Oracle tablespaces, tables, and indexes.
                                                                                  Fast Unload for Oracle allows users to tailor the format of the data output, ensuring that the file is ready for its intended purpose without further manipulation.
                                          The user can also determine the rows and columns being extracted.




Author Comment

ID: 2629970

then you think there is no other way than buy a 3rd party tool ?

I´ll leave open this question for another comments. If there are not more likely comments, I´ll give you the points of the answer.


Expert Comment

ID: 2629977

Expert Comment

ID: 2629981
P.S. This is the reason 3td party tools exists. They do something, original tools doesn't.

Expert Comment

ID: 2630044
rwarsh is right.
There is no way you can do that with the tools/utilities oracle offer.

Oracle's only data downloading utility is the export utility.

Of-course 3rd party companies can use the database tables or even the datafiles themselves to create other fast exporting utilities.

If you still don't want to use 3rd party tools, and the spooling data from sqlplus is good for you, you can still automate the spooling process in sqlplus using  simple scripts.


Expert Comment

ID: 2633024
Before you try 3rd party tools, you should try to squeeze the most out of Oracle's export utility.

Try doing the export with the DIRECT=Y directive and set RECORDLENGTH appropriately.

Note that there are restrictions for using direct mode export.

From Oracle Documentation:

Conventional path Export uses the SQL SELECT statement to extract data from tables.
Data is read from disk into a buffer cache, and rows are transferred to the evaluation
buffer. The data, after passing expression evaluation, is transferred to the
Export client, which then writes the data into the export file.

Direct path Export extracts data much faster than a conventional path export.
Direct path Export achieves this performance gain by reading data directly,
bypassing the SQL Command Processing layer and saves on data copies whenever possible.

For added performance, you can set the database to direct read mode. This eliminates
contention with other users for database resources because database blocks are read
into the Export session's private buffer, rather than into a public buffer cache.
For more information about direct read mode, see the Oracle8 Administrator's Guide.

In a direct path Export, data is read from disk into the buffer cache and rows are
transferred directly to the Export client. The Evaluating Buffer is bypassed.
The data is already in the format that Export expects, thus avoiding unnecessary
data conversion. The data is transferred to the Export client, which then writes
the data into the export file.

To reduce contention with other users for database resources during a direct path Export,
you can use database direct read mode. To enable the database direct read mode, enter the
following in the INIT.ORA file:

  compatible = <db_version_number>,
  The db_version_number must be 7.1.5 or higher.

You may improve performance by increasing the value of the RECORDLENGTH
The following values are generally recommended for RECORDLENGTH:

  multiples of the file system I/O block size
  multiples of DB_BLOCK_SIZE


You cannot use direct path Export to export rows that contain LOB, BFILE, REF, or
object type columns, including VARRAY columns and nested tables.
Only the data definition to create the table is exported, not the data.

Expert Comment

ID: 2633878

 How this answers original question?

i.e "export tables (or the result from a Select statement) from Oracle to a text file"

Expert Comment

ID: 2634236
Oops, didn't notice the specification was explictly for *text* file.
Jury will disregard my previous comment ...

Unleash the 3rd party tools.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Expert Comment

ID: 2634680
Well.. you still could use the spool option, but formating the data at the same time that you list it. ie:

SELECT "" || DsName || "", "" || DsLastName FROM Users

(I'm not sure if the amounts of " is rigth)

It should leave in your file something like this:

<--- File content -->

SQL>SELECT "" || DsName || "", "" || DsLastName FROM Users

"" || DsName || "", "" || DsLastName
"Heinz", "Frentzen"
"Ramiro", "Reyes"

(2 rows retrieved)

<--- End of File -->

So you only have to delete the info that u don't want and keep the rest of it, having the fields delimited by " and the rows by ENTERs.

I hope it might be usefull :-)

Expert Comment

ID: 2634682
Sorry, the SQL should be:

SELECT '"' || DsName || '", "' || DsLastName FROM Users

Expert Comment

ID: 2634699
For a faster and economic way,

you can use a free third party tools

TOAD - Tool for Oracle application Developer

It is very user friendly tools to export table's data from database.


Expert Comment

ID: 2634878

 Use of TOAD is sililar to spooling. To get fast unload TOAD is useless.

LVL 35

Expert Comment

by:Mark Geerlings
ID: 2636209
I think you can see by now that there isn't an easy, cheap way to export Oracle data to an ASCII file.  I've had very good results with utl_file with tables of up to 1 - 2 million rows.  (I haven't needed to with larger tables).  That only works though with "standard" data types (text, number and/or date and even with dates you have to be careful of the format mask).

There are at least a couple of advantages with utl_file approach:
1. you can sort the data, if you want to, when you build the ASCII file

2. your main cursor can be based on just a small range of primary key values and that can be inside a loop that increments the range, then gets the next set, so you avoid the "snapshot too old..." error if the table has other activity at the time.

I've also heard of good results with the third-party tools from Platinum and BMC, but they are not cheap.

Author Comment

ID: 2645196
Alex.. I already try TOAD, but it´s not faster and the main problem with TOAD is that I can´t export data in automatic way (like a call from a program or something like that).


To other friends:

Well I think the 3dParty Tool can give me speed but can´t give me automatic generation from a "program-call". Then I conclude based on your comments, that the very fast way is make (like Im doing right now) a PRO*C/C++ program for my purposes. This is faster than UTL functions for reading/writing (based on my tests).

I want still now if what sbenyo says about make a script to spool can be called from an "outside" program (PL/SQL, PRO*C, etc).


Accepted Solution

rwarsh earned 50 total points
ID: 2645515


  I beleive, my information fully answer your original question.

  As you told yourself: "If there are not more likely comments, I´ll give you the points of the answer."


P.S. You defenitely can call 3rd party tools from your program.

Expert Comment

ID: 2645972
Thanks for assigning points to me, but why grade B?




Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now