question for JDBC Oracle gurus

LinoRichie

Hi Folks,

Hope someone can help here.

I'm working on a program that, among other things, takes relatively large volumes of data e.g. thousands of lines and loads them to an Oracle database. Each line is passed to a Stored Procedure on the DB that does a bit of processing and inserts the line into a DB table.

I'm calling the Stored Procedure from a J2EE environment. I'm trying to re-write the Java code to improve performance which is pretty poor at present.

I was hoping to use some kind of batch processing using the oracle.jdbc.driver library using stuff like setDefaultExecuteBatch(), executeUpdate() and sendBatch().

I have 3 questions really:

1. However, this type of batch processing seems to only work with OraclePreparedStatement objects and not OracleCallableStatement objects - is this right?

2. And because I am calling a Stored Procedure and not passing SQL directly to the DB, I have to use OracleCallableStatement and not OraclePreparedStatement - is this right?

3. And if I am right in these 2 observations, has anyone any way to tackle batch processing using OracleCallableStatements?

Thanks.

cousin_borat

LinoRichie wrote: »

Hi Folks,
I was hoping to use some kind of batch processing using the oracle.jdbc.driver library using stuff like setDefaultExecuteBatch(), executeUpdate() and sendBatch().
Thanks.

I was faced with a somewhat similar problem some time ago for a client integration project.

Instead of using JDBC directly installed Spring in the Apps lib on the Weblogic server hosting the J2EE app which was accessing Oracle 9i. Spring has a suite of JDBC convenience classes that take care of the transaction management and closing connections, etc etc. It should have the ability to do batch calls although I havent used that functionality directly.

If you can't go that route then I can take a look at the JDBC stuff, theres a good book called "Oracle Database Programming using Java and Web Services" that covers the topic extensively.