Results 1 to 5 of 5

Thread: Updating Oracle table from a text file.

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2007
    Location
    Kerala, India
    Posts
    275

    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.

  2. #2
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2007
    Location
    Kerala, India
    Posts
    275

    Re: Updating Oracle table from a text file.

    Quote Originally Posted by abhijit View Post
    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.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2007
    Location
    Kerala, India
    Posts
    275

    Re: Updating Oracle table from a text file.

    Quote Originally Posted by abhijit View Post
    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.

  5. #5
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    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
  •  



Click Here to Expand Forum to Full Width