Execute SQL Function CallableStatement JDBC

In the following program a function by name calculate() is executed from JDBC. The function includes one IN parameter, one OUT parameter and one return value (note that return value can be one only, but IN and INOUT can be of any number).

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

Example on Execute SQL Function CallableStatement JDBC

SQL>  create or replace function calculate(x in number, y out number) return number as
z number;
begin
y:=x*x;
z:=x*x*x;
return z;
end;
/

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

import java.sql.*;
public class CallFunction
{
  public static void main(String args[]) throws Exception
  {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott", "tiger");
                                           // replace Statement with CallableStatement
    CallableStatement cst=con.prepareCall("{ ? = call calculate(?,?) }");

    cst.registerOutParameter(1, Types.INTEGER);     
                                           // return type also must be registered
    cst.registerOutParameter(3, Types.INTEGER);      
                                           // OUT parameter
    cst.setInt(2, 10);                     // IN parameter

    cst.execute();

    int k = cst.getInt(1);                 // retrieving return value
    int m = cst.getInt(3);                 // retrieving OUT parameter value

    System.out.println("The return value is " + k);
    System.out.println("The out value is " + m);

    cst.close();
    con.close();
  }
}

Output screenshot on Execute SQL Function CallableStatement JDBC

CallableStatement cst=con.prepareCall(“{ ? = call calculate(?,?) }”);

1st question mark is return value, the 2nd question mark represents IN parameter, and the 3rd question mark is for OUT parameter. Both OUT and return values should be registered with CallableStatement using registerOutParameter() method.

A program exists for executing stored procedure at Executing stored procedure 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.

Note: To know how to use try-catch-finally with the above code, example program is available at JDBC Example – Create Table.