Overview of CallableStatement Objects

This section provides overview information on CallableStatement Objects for stored procedures.

JDBC CallableStatement objects are designed to represent calls to stored procedures in the database server. Several things you need to remember about CallableStatement objects:

1. A CallableStatement object is created by calling the prepareCall() method on a Connection object like:

// Calling a procedure with no procedure parameters
  CallableStatement cs1 = con.prepareCall("CALL Abc()");

// Calling a procedure with some literal parameters
  CallableStatement cs2 = con.prepareCall("CALL Abc(v1, v2, ...)");

2. Place holders (question marks "?") can be used in call statements in the same way as in prepared statements. OUT (output) parameters, INOUT (input and output) parameters, and return parameter must be represented by place holders:

// Calling a procedure with place-holder parameters
  CallableStatement cs3 = con.prepareCall(
    "CALL Abc(v1, v2, ?, ?, ...)");

// Calling a procedure with a return value
  CallableStatement cs4 = con.prepareCall(
    "? = CALL Abc(v1, v2, ?, ?, ...)");

The last format "? = CALL Abc(v1, v2, ?, ?, ...)" does not apply to Oracle, since Oracle procedures are not allowed to return values.

3. If an IN parameter is represented by a place holder, its value should be provided by calling the setXXX() method before the execution of the call statement:

// providing a string value to the first place holder
  cs3.setString(1, "North Pole");

// providing a double value to the second place holder
  cs3.setDouble(2, 3.14);

4. Place holders for OUT, INOUT and return parameters must registered as output parameters with JDBC data types before the execution of the call statement:

// Registering the first place holder as a INTEGER output
  cs4.registerOutParameter(1, java.sql.Types.INTEGER);

// Registering the fourth place holder as a DATE output
  cs4.registerOutParameter(4, java.sql.Types.DECIMAL, 3);

5. If no ResultSet objects is returned from the stored procedure, the call statement should be executed by calling the cs.executeUpdate() method.

// Executing the call statement
  cs1.executeUpdate();

6. If one or more ResultSet objects are returned from the store procedure, the call statement should be executed by calling the cs.executeQuery() method. It will return the first ResultSet object, representing the output of the first query statement executed in the stored procedure:

// Executing the call statement
  ResultSet res = cs1.executeQuery();

7. A CallableStatement can also be executed in batch mode similar to a PreparedStatement:

// Close one copy of the call statement
  cs3.setXXX(...);
  cs3.addBatch();

// Close another copy of the call statement
  cs3.setXXX(...);
  cs3.addBatch();

// Execute all copies of the call statement in batch mode
  cs3.executeBatch();

8. After the execution of the call statement, all rows of all returning ResultSet objects should be processed before retrieving any registered output parameters.

// Executing the call statement
  ResultSet res = cs1.executeQuery();
  while (res.next()) {
    ...
  }

9. After finishing all ResultSet objects, registered output parameters can be retrieved by using the getXXX() methods:

// Retrieving registered output parameters
  int first = cs4.getInt(1);
  Date four = cs4.getDate(4);

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Oracle Express Edition Installation on Windows

 Oracle JDBC Drivers

 Oracle - Reference Implementation of JdbcRowSet

 Oracle - PreparedStatement

Oracle - JBDC CallableStatement

Overview of CallableStatement Objects

 "CREATE PROCEDURE" - Creating a Simple Procedure

 Creating Procedures with IN and OUT Parameters

 Creating CallableStatement Objects with prepareCall()

 Creating CallableStatement Objects with Parameters

 getProcedures() - Listing Stored Procedures

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Archived Tutorials

 References

 Full Version in PDF/EPUB