JDBC: Calling a Stored Procedure
 

Calls to Stored Procedures and Functions are a bit more involved than SQL queries and updates. These calls are performed using the CallableStatement? interface, which is an extension of the PreparedStatement? interface. Parameters passed to and from the stored procedure are identified using ? placeholder symbols. These placeholders must be registered as IN our OUT data elements, using data types from the Types class.

Create the Command with Placeholders:
// Stored procedure call
String command = "{call PROCEDURENAME(?,?,?,?)}"; // 4 placeholders
// Stored function call
String command = "{? = call FUNCTIONNAME(?,?,?,?)}"; // 4 placeholders + 1 return value
Create a Callable Statement Object:
CallableStatement? cstmt = con.prepareCall (command);
(Throws a SQLException error.)

Register the IN and OUT Placeholders:
// Assign IN parameters (use positional placement)
cstmt.setInt(1, 50); // first placeholder has an integer value of 50
cstmt.setString(2, "Smith"); // second placeholder has a string value of "Smith"
cstmt.registerOutParameter(3, Types.NUMERIC); // NUMERIC is preferred over INTEGER with some databases
cstmt.registerOutParameter(4, Types.VARCHAR); // fourth placeholder is an OUT field of type VARCHAR.
(Throws a SQLException error.)

Note: INOUT fields may be registered using the setXXXX() and registerOutParameter() methods for the same placeholder position. Array parameters (i.e. PL/SQL Tables) used in Oracle stored procedure and function calls are not supported by the JDBC.
Execute the Procedure or Function Call:
cstmt.execute();
(Throws a SQLException error.)

Process the OUT Placeholders:
/** Use proper getXXXX() method based on parameter data type.
* Placeholder 3 will receive a NUMERIC value as a BigDecimal? object with 0 decimal places.
* Placeholder 4 will receive a VARCHAR value as a String object.
*/
BigDecimal? num = cstmt.getBigDecimal(3,0);
String str = cstmt.getString(4);
(Throws a SQLException error.)

Close the CallableStatement? Object:
// Close the call object after update is complete
cstmt.close();
(Throws a SQLException error.)