Home » SQL & PL/SQL » SQL & PL/SQL » Populating Text File with records from a table
Populating Text File with records from a table [message #200050] Fri, 27 October 2006 12:15 Go to next message
desigan
Messages: 8
Registered: October 2006
Junior Member
Hi,

I have a table with 25 million rows. I need to sort these rows of the table and then copy all the records into a text file.

Following are the ways that I tried out.

1) I wrote an unix ".ksh" file, which will execute the select query and move the select query contents to a file.

It is like the following:
Select * from table > TextFile.txt

It takes 50 mintues to copy the entire data to this "TextFile.txt".

2) This is another idea, which I want to try out. Instead of writing the entire contents to a file, I plan to do the following:
a) Create 5 Threads (say in java)
b) Each thread will operate on 5 million rows and write them to a separate text file. All these threads will run in parallel.
c) At the end of all the threads, we will have 5 files, each with 5 million rows.
d) Concat these 5 files together.

I feel this should work faster than the earlier one.

Apart from the above two ways, Is there any other way by which I can achieve this (which will be faster too)? This is a daily job and hence I am looking out for performance here.

Regards,
Desigan
Re: Populating Text File with records from a table [message #200054 is a reply to message #200050] Fri, 27 October 2006 12:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>) Create 5 Threads (say in java)
Java is not a good option.
Databases are designed to do this. If you do this in java, first you need to fetch the data out and then write.
Try any of these methods.
http://asktom.oracle.com/~tkyte/flat/index.html
Re: Populating Text File with records from a table [message #200207 is a reply to message #200054] Sun, 29 October 2006 05:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And in sql*plus, increase the array size.
Sql> SET ARRAYSIZE N

If there is a HWM (highwater mark ) problem, fix it first.
Search this board for highwater mark.
Re: Populating Text File with records from a table [message #200367 is a reply to message #200207] Mon, 30 October 2006 09:30 Go to previous messageGo to next message
desigan
Messages: 8
Registered: October 2006
Junior Member
Mahesh,

Thanks a lot for your replies. I went through the URL you specified.

I feel the following two are meeting my requirement needs:

1) Using PL SQL utl_file to write the data

2) There is a may mentioned by using SQL Plus. It is followed by some query as follows:

$ sqlldr_exp scott/tiger dept
LOAD DATA
INFILE *
INTO TABLE dept
.........

$ sqlldr_exp scott/tiger dept > dept.ctl

Could you please explain how to run this? I tried running these lines in a SQLPLUS window but it gave me error as "unknown command".

[Sorry that I am new to Oracle and hence not able to follow your hint.]

Regards,
Desigan
Re: Populating Text File with records from a table [message #200369 is a reply to message #200367] Mon, 30 October 2006 09:41 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

sqlldr not sqlplus command.
if you want to run in sqlplus prompt.

try this way

sql>host sqlldr { list of parameter }

or

You can also run command prompt.
cmd>sqlldr { list of parameter }

hope this helps.
Re: Populating Text File with records from a table [message #200372 is a reply to message #200367] Mon, 30 October 2006 09:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You cannot right away use it.
sqlldr_exp is a script you need to download from the said URL. Donwload the zip file , tweak it for your use.
By the end of the page you will find
Quote:
You can download the UNIX and NT scripts here. They are in a a file "unloader.zip" which can be opened with WinZip 6.0 or up on windows.
Re: Populating Text File with records from a table [message #200376 is a reply to message #200372] Mon, 30 October 2006 09:55 Go to previous message
desigan
Messages: 8
Registered: October 2006
Junior Member
Thanks a lot for your immediate replies. I shall do the same.

Regards,
Desigan
Previous Topic: SQL Query Problem
Next Topic: Order by problem
Goto Forum:
  


Current Time: Sat Dec 10 06:57:46 CST 2016

Total time taken to generate the page: 0.09196 seconds