Execute procedure with CallableStatement JDBC

In the following program a procedure called getMe is executed using CallableStatement where a value is sent and another value is accessed. Here x is IN number and y is OUT number.

CallableStatement along with PreparedStatement is fully discussed in Difference between Statement, PreparedStatement, CallableStatement.

Example on Execute procedure CallableStatement

Following is the SQL procedure.

SQL>  create or replace procedure getMe(x in number, y out number) as

See this procedure exists in the database before the following JDBC program is executed.

import java.sql.*;
public class CallProcedure
  public static void main(String args[]) throws Exception
    Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott", "tiger");
                                              // replace Statement with CallableStatement

    CallableStatement cst =  con.prepareCall("{ call getMe(?,?) }");

    cst.registerOutParameter(2, Types.INTEGER);

    st.setInt(1, 30);

    cst.execute();                            // this Execute procedure CallableStatement

    int k = cst.getInt(2);
    System.out.println("The out parameter value is " + k);


Execute procedure CallableStatement
Example on Execute procedure CallableStatement

prepareCall(String) is a method of Connection interface that returns an object of CallableStatement.

setInt() method is inherited by CallableStatement from PreparedStatement interface.

OUT parameter should be registered with CallableStatement object using registerOutParameter() method.

Note what method of Connection interface returns the objects 3 statements.

1. createStatement() of Connection interface returns an object Statement.
2. prepareStatement() of Connection interface returns an object PreparedStatement.
3. prepareCall() of Connection interface returns an object CallableStatement.

A program exists for executing function at Executing SQL Function with CallableStatement Interface.

Note: If the JDBC basics are not clear, it is advised to go through JDBC Programming Basic Steps where the meaning of Connection, Statement etc. are discussed.

Leave a Comment

Your email address will not be published.