Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: batching of stored procedures

    Hi all,
    I've a good experience with DB2 and SQL Server, but I'm new to Oracle.

    Now I'm porting my ODBC application written in C++ to Oracle and I'd like to know if with Oracle I can execute a batch of stored procedures via ODBC.
    The stored procedures have input parameters that we bind using parameter markers.

    In SQL Server I have this capability, in DB2 I just have the possibility to do batch inserts/updates but I cannot batch stored procedures from ODBC/C++ code.

    From my first Internet search I found that batching of insert statements is supported in Oracle.
    This is shown for example in the bulkinsert.c file from the link:
    TimesTen ODBC Sample Programs

    Is it possible to do same thing but using calls to stored procedures instead of insert statements?

    Thank you very much for your help!
    Robert

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I don't think you can batch stored procedures.

    Btw: the link you posted is not for the Oracle database. TimesTen is a completely different product!
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    thanks shammat and sorry for the wrong link, but at least it's useful to show what I mean....

    Could you please confirm that batch inserts/updates via ODBC are supported in Oracle?

    Are there some examples or useful links I can read about it?

    Thanks again!
    Robert

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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
    Mar 2012
    Posts
    120
    I didn't find any example of stored procedures batching looking at the bulk load documentation....

    there's something about java and JDBC there, but I need ODBC support for this

    Thanks

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Not exactly sure what you mean by batching.... but you can submit multiple procedure calls in a single Oracle SQL command.
    Code:
    begin
       myproc(:param1, :param2);
       anotherproc(:param3, :param4);
       moreprocs(:param5, :param6);
    end;

  7. #7
    Join Date
    Mar 2012
    Posts
    120
    I'm referring to the possibility to execute calls to stored procedures from C++ via ODBC not once at a time but in chunks of "batchsize".

    In DB2 this is achieved setting the SQL_ATTR_PARAM_BIND_TYPE attribute to the single parameter record size and the SQL_ATTR_PARAMSET_SIZE attribute to the batchsize.

    Then when the SQLExecDirect is executed for a call to the stored procedure, it's executed batchsize times.

    Of course the parameters must be stored in records (each record is a complete set of values for one call) in a contiguos memory area (like a struct).

    This improves a lot performances, so I'd like to know if Oracle supports this.

    Thanks

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I have done something like this with ADO.NET, so Oracle can definitely do it. But I have never tried it with ODBC and certainly don't know too much about C++.
    This should be what you are looking for though: bulkinsert.c
    I am no C++ fundi, but it looks like the code is creating array parameters and executing insert statements in bulk mode. Hope it helps.

    Edit. Looks like that's the same one you submitted. It's weird but all batch ODBC information for Oracle seems to be about TimesTen. Oh well, sorry I couldn't help.
    Last edited by dayneo; 08-09-12 at 11:53. Reason: I'm a dumbass... :)

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    PL/SQL should be avoided when task can be completed using only plain SQL
    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.

Posting Permissions

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