Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    8

    Unanswered: How to do bulk insert in ProC?

    Hi,

    I am new to Oracle and I need to write a ProC application to regularly load several thousand records from C++ arrays to different Oracle tables.

    I am not sure which way would be more efficient:

    1. Use PL/SQL FORALL to load data:
    forall index in 1 .. :numRows
    insert into myTable (col1, ...) values ( :myArray(i), ..)


    2. Use dynamic sql method 4: prepare a insert statement with bind variables for the arrays.

    I think this may be more flexible as I may be able to dynamically build an insert statement for different tables. But I do not know if the performance is good. In this method, does Oracle load data row by row or does a bulk insert?

    Or any other better way?

    Thanks for any advice.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If loading less than 10,000 at a time, you probably will not be able to measure any difference between the 2 methods
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2007
    Posts
    8
    Hi Anacedent,

    You are right. I managed to write a small program to use both methods to load data and cannot say which one is faster. The PL/SQL FORALL is easier to code with fewer steps. I wonder if both methods are implemented in similar way to load data.

    Thanks for you response.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suggest the "best" way to load external data is via External Tables.
    I understand you are dealing with a C++ program.
    I suspect it obtained the data from some OS resident file.

    The reality is that no 1 size fits all solution exists.
    One needs to choose the best tool for the job at hand.
    When your only tool is a hammer, all problems are viewed as nails.
    A hammer is a suboptimal tool to make 2 boards from 1 piece of lumber.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2007
    Posts
    8
    The applicatin is to load real time trade data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •