Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Creating/Exporting to a text File from Oracle

Posted on 2000-03-17
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +4

Expert Comment

by:Ron Warshawsky
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

by:Ron Warshawsky
ID: 2629977
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!


Expert Comment

by:Ron Warshawsky
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

by:Ron Warshawsky
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.

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

by:Ron Warshawsky
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

Ron Warshawsky earned 150 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

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




Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

597 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