Solved

Creating/Exporting to a text File from Oracle

Posted on 2000-03-17
16
656 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
  • 7
  • 2
  • 2
  • +4
16 Comments
 
LVL 3

Expert Comment

by:rwarsh
Comment Utility
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
Comment Utility
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:rwarsh
Comment Utility
Okey.
0
 
LVL 3

Expert Comment

by:rwarsh
Comment Utility
P.S. This is the reason 3td party tools exists. They do something, original tools doesn't.
0
 
LVL 5

Expert Comment

by:sbenyo
Comment Utility
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
Comment Utility
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:rwarsh
Comment Utility
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
Comment Utility
Oops, didn't notice the specification was explictly for *text* file.
Jury will disregard my previous comment ...

Unleash the 3rd party tools.
0
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.

 
LVL 1

Expert Comment

by:epretti
Comment Utility
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
Comment Utility
Sorry, the SQL should be:

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

Expert Comment

by:alexvii
Comment Utility
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:rwarsh
Comment Utility
alex,

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

rwarsh
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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:
rwarsh earned 50 total points
Comment Utility

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:rwarsh
Comment Utility
Thanks for assigning points to me, but why grade B?

Regards,

  rwarsh


 
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle rollup query 3 34
query returning everything 11 65
PL/SQL - Leading zeros 7 39
Dataware house query tuning 9 29
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 …
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

772 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

12 Experts available now in Live!

Get 1:1 Help Now