?
Solved

Creating/Exporting to a text File from Oracle

Posted on 2000-03-17
16
Medium Priority
?
663 Views
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 ?
0
Comment
Question by:NetoMan
[X]
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
16 Comments
 
LVL 3

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.

Regards,

  rwarsh

0
 
LVL 2

Author Comment

by:NetoMan
ID: 2629970
rwash:

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.

0
 
LVL 3

Expert Comment

by:Ron Warshawsky
ID: 2629977
Okey.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 3

Expert Comment

by:Ron Warshawsky
ID: 2629981
P.S. This is the reason 3td party tools exists. They do something, original tools doesn't.
0
 
LVL 5

Expert Comment

by:sbenyo
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.

0
 
LVL 2

Expert Comment

by:cadabra
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


RESTRICTIONS:

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.
0
 
LVL 3

Expert Comment

by:Ron Warshawsky
ID: 2633878
Cadabra,

 How this answers original question?

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

Expert Comment

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

Unleash the 3rd party tools.
0
 
LVL 1

Expert Comment

by:epretti
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 :-)
0
 
LVL 1

Expert Comment

by:epretti
ID: 2634682
Sorry, the SQL should be:

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

Expert Comment

by:alexvii
ID: 2634699
For a faster and economic way,

you can use a free third party tools

TOAD - Tool for Oracle application Developer

http://www.toadsoft.com/downld.html

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

0
 
LVL 3

Expert Comment

by:Ron Warshawsky
ID: 2634878
alex,

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

rwarsh
0
 
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.
0
 
LVL 2

Author Comment

by:NetoMan
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).

Thanks...

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).

0
 
LVL 3

Accepted Solution

by:
Ron Warshawsky earned 150 total points
ID: 2645515

Netoman,

  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."

rwarsh

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

Expert Comment

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

Regards,

  rwarsh


 
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

771 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