Call a Oracle Procedure in a Java Batch containing Insert and Update Queries (Statement)

Question

I am working on a Java based project, where things are implemented in the following way

• There are some classes (A,B,C,D....) which takes Hash Map having key as an Integer and value as an array list of String. Every String element in the arraylist is a Oracle SQL Query (INSERT or UPDATE)
• These methods of classes (A,B,C,D....) loops through the Map. All the SQL Queries of a particular key are added to a batch using JDBC Statement (**stmt.addBatch(String)**). For every unique key, there is a separate batch created and executed.
• There are many classes (X,Y,Z...) which are working on populating this Map and arraylist of Strings to put different INSERT and UPDATE Queries

Now, I need to add an Oracle procedure call for some keys and hence should be added to the String Arraylist for the respective keys.

I tried adding the following strings to call procedures, but no one seems to work for me.

1. EXEC INSERT_AUTONUMBER_DATA(......)

2. BEGIN INSERT_AUTONUMBER_DATA(........) END;

I don't want to modify much of the code, as it might impact the other areas.

I can add a Callable Statement in the batch, but for this, I will need to modify all the methods and classes which takes responsibility for preparing the batch which are around 15 in my project (Project Design is not very good), plus I will have to write a condition identifying a procedure call, so that I will prepare a callable statement instead of a Statement.

Is there any other way of doing it, without changing the classes executing the batches?

Answer 1

I tried the following call statement to add in the batch.

CALL INSERT_AUTONUMBER_DATA(......)

This String is getting added in a Statement with the following logic and it works.

Statement s = conn.createStatement();
s.addBatch("CALL INSERT_AUTONUMBER_DATA(......)");
s.addBatch(.....);
.
.
.

s.executeBatch();

So, here I am able to call a procedure via java.sql.Statement in a Batch