10 Responses

1. Laks says:

   May 18, 2008 at 7:34 am

   Hi,
   Does the latest DBD::Oracle have features like batching and Oracle types support features similar to the jdbc thin driver provided by oracle.

   thanks and Regards
2. John Scoles says:

   May 18, 2008 at 2:21 pm

   not sure what exactly you mean by batching and Oracle type support.

   Do you have any examples?
3. Laks says:

   May 19, 2008 at 3:14 am

   Thanks for your response.
   1) I am referring to the facility provided by oracle jdbc thin driver to batch the insert/update operations so that roundtrips to the server is reduced.
   eg: One could set the batch parameter on the connection or on a specific statement object/handle to a value say 10. Then irrespective of the # of calls to execute a update/insert on the statement handle, every 10 update/insert requests get batched and sent to DB server to execute. This reduces the round trips to server which normally happens for every insert/update that gets sent to server to execute each time on execute call.
   I am not aware how this works in DBD::Oracle.
   2) How can one retrieve pl/sql Table type or bind such things with DBD::Oracle.
   JPublisher provided by oracle maps between oracle types and jdbc types easily.

   Thanks and regards
4. John Scoles says:

   May 19, 2008 at 9:08 am

   For #1 I think you are refering to the OCI Array_interface and yes DBD::Oracle supports it (since 1.18)
   check out this link
   http://search.cpan.org/~timb/DBI-1.604/DBI.pm#bind_param_array

   As for #2 I think we support is as well check out these links
   http://search.cpan.org/~pythian/DBD-Oracle-1.21/Oracle.pm#Returning_A_Recordset
   http://search.cpan.org/~pythian/DBD-Oracle-1.21/Oracle.pm#Binding_Cursors
5. Laks says:

   May 27, 2008 at 9:28 am

   Thanks John for your responses.
   1) I had a look at the bind_param_array and execute_array interfaces added from 1.18 of DBD::Oracle.
   Though it helps in array binding and helps it is not equivalent to the batching facility in java driver.With jdbc one can transparently batch inserts and updates to be sent to rdbms server in one shot. inserts and updates are done normally but driver internally batches them together like one unit of work.
   Anyway i am happy that the array interface helps in binding array of values for inserts from 1.18.
6. Sergey says:

   May 31, 2008 at 5:14 am

   Thank you very much for patching Makefile.PL!
   It’s very easy now to install DBD::Oracle with 11g.
7. John Scoles says:

   July 17, 2008 at 8:08 am

   Laks Said

   ‘Though it helps in array binding and helps it is not equivalent to the batching facility in java driver.’

   Hi Laks I was curious about this so I had a look under the hood of the JDBC interface and which calls it was using in OCI to accomplish the task.

   Well to my surprise both JDBC and DBD::Oracle use almost the exactly the same code under the hood so I think it is a case of ‘We do it this way any you do it the wrong way’:);).

   In JDBC you
   1) prepare a statement (con.prepareStatement)
   2) bind values to the statement (stmt.setString(2, “bla”))
   3) add the statement to a batch (stmt.addBatch())
   4) repeat 2-3 above and then
   5) execute your batch. (stmt.executeBatch())

   in DBD:Oracle
   1) prepare the statement ($dbh->prepare)
   2) bind all the values to the statement ($sth->bind_param_array)
   3) execute the statement. ($sth->execute_array)

   In both JDBC and DBD::Oracle only 1 round trip call to the execute is made in OCI.

   So me thinks it is a case here of ‘You say ‘potato’ and I say ‘potato’.

   cheers
8. Paul Vallee says:

   July 17, 2008 at 12:00 pm

   John,
   I think Laks is saying ‘potato’ and you’re saying “pineapple”.
   The array interface is only for batching re-use of one handle for multiple uses, saving round trips. The batch interface lets you batch multiple statements, including different ones, into one database call.
   No?
   Paul
9. John Scoles says:

   July 17, 2008 at 1:45 pm

   Actully ‘No’ is correct.

   The JDBC interface does let you ‘batch’ multiple statments with one execute but this is just hiding the fact that one execute is fired for ecach of them.

   From the JDBC docs

   “The batch update facility is used with a PreparedStatement to associate multiple sets of input parameter values with a single PreparedStatement object.”

   so this

   PreparedStatement stmt = con.prepareStatement(
   “INSERT INTO employees VALUES (?, ?)”);

   stmt.setInt(1, 2000);
   stmt.setString(2, “Kelly Kaufmann”);
   stmt.addBatch();

   stmt.setInt(1, 3000);
   stmt.setString(2, “Bill Barnes”);
   stmt.addBatch();

   // submit the batch for execution
   int[] updateCounts = stmt.executeBatch();

   will do 1 OCIExecute .

   but this

   Statement stmt = con.createStatement();
   stmt.addBatch(”INSERT INTO employees VALUES (1000, ?,?)”);
   stmt.setInt(1, 2000);
   stmt.setString(2, “Kelly Kaufmann”);
   stmt.addBatch(”INSERT INTO departments VALUES (?, ?’)”);
   stmt.setInt(1, 260);
   stmt.setString(2, “Shoe”);
   stmt.addBatch(”INSERT INTO emp_dept VALUES (?, ?)”);
   stmt.setInt(1, 1000);
   stmt.setInt(2, 260);

   int[] updateCounts = stmt.executeBatch();

   will do 3 OCIExecute command one for each SQL. So in this case there are 3 round trips to the server rather than just one for the last example.
10. Paul Vallee says:

    July 17, 2008 at 1:57 pm

    Excellent info, John, thanks.
    Paul