-
Oct 31st, 2013, 08:45 PM
#1
Thread Starter
Hyperactive Member
Updating Oracle table from a text file.
Hi,
I am new to the database forum.
I would like to know an efficient way to load data into an Oracle table from a text file.
I am using VB6 and ADO. Presently I am using recordset. But it takes a long time to
insert some 150000 rows. The text file is located on the client machine and Oracle is on the server.
Any help is appreciated.
-
Nov 4th, 2013, 04:55 PM
#2
Re: Updating Oracle table from a text file.
The quickest way is use Sql loader or Data Pump (depending on the version of Oracle you're on).
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
-
Nov 5th, 2013, 08:27 PM
#3
Thread Starter
Hyperactive Member
Re: Updating Oracle table from a text file.
Originally Posted by abhijit
The quickest way is use Sql loader or Data Pump (depending on the version of Oracle you're on).
Thanks abhijit.
I will try it out and see whether there is any improvement in speed.
-
Nov 22nd, 2013, 11:40 AM
#4
Thread Starter
Hyperactive Member
Re: Updating Oracle table from a text file.
Originally Posted by abhijit
The quickest way is use Sql loader or Data Pump (depending on the version of Oracle you're on).
I dont lnow how to make use of these in VB6.
So I did some searching a found out that probably the fastest method is to
1. Upload the file as CLOB to a temporary table.
2. Write a package to split the CLOB and insert into the table.
I am polishing up the code. Will post it after its done.
-
Nov 22nd, 2013, 02:08 PM
#5
Re: Updating Oracle table from a text file.
You'll then discover that dealing with clobs, esp when it comes to debugging your code is no easy task.
How long does it take to insert records?
Are you using the following method?
1) Read all the records from the txt file (150 K rows) into an array.
2) Use a command object with parameters.
3) loop through the array and use the execute method on the command object.
If you had ADO .NET, you would have had better luck with doing bulk inserts.
Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
save a blobFileStreamDataTable To Text Filemy blog
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|