JDBC CallableStatement - call multiple stored procedures in one shot

Question

To call Oracle's stored procedure using only simple JDBC in Java we could do something like this:

con.prepareCall("{ call ? = my_procedure(?) }");
callStm.registerOutParameter(1, Types.INTEGER);
callStm.setInt(2, -100);
callStm.execute()

I want to know is it somehow possible to call more then one procedure inside one prepareCall statement? For example:

con.prepareCall("{ call prepare_proc(); ? = my_procedure(?) }");

Of course it is not valid, but how to fix it and call two or more procedures in one shot?

Answer 1

You could wrap them in an anonymous block:

con.prepareCall("begin prepare_proc(); ? := my_procedure(?); end;");

Read more about the differences.

The assignment of the the function's return value to the bind variable (the first ?) is now a PL/SQL assignment, within the anonymous block, so it uses ? := .... In your original code you were using the call SQL statement version (? = call ...), and JDBC uses a plain = for the assignment to the bind variable.

Presumably my_procedure is actually my_function, otherwise it won't have a return type...

It would probably be more maintainable to have a wrapper procedure (or function) which makes both calls, and then just call that single wrapper over JDBC.