Calling Stored Procedures

Oracle OCI supports two classes of stored procedures:

• To execute parameterized stored procedure calls, use CallableStatement objects.
• To execute stored procedures as SQL statements using the EXEC syntax, use Statement objects.

Using Callable Statement Objects

You can use the generic execute method of the CallableStatement class to execute any stored procedure as a parameterized query.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the SearchSuppliers stored procedure:

CallableStatement cstmt = conn.prepareCall("SearchSuppliers");
cstmt.setString("Country", "US");
boolean ret = cstmt.execute();   
if (!ret) {
  int count=cstmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=cstmt.getResultSet();
  while(rs.next()){
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
    }
  }
}

Supporting PL/SQL

Oracle OCI supports the execution of PL/SQL stored proecdures and anonymous blocks using either SQL92 escape syntax or Oracle PL/SQL block syntax.

SQL92 Escape Syntax

CallableStatement cs1 = conn.prepareCall
( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
( "{? = call func (?,?)}" ) ; // stored func

Oracle PL/SQL Block Syntax

CallableStatement cs3 = conn.prepareCall
( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
( "begin ? := func(?,?); end;" ) ; // stored func

Example to Call Function

CREATE OR REPLACE FUNCTION FuncSum (arg1 IN INT, arg2 IN INT, arg3 IN INT)
RETURN NUMBER IS
BEGIN
RETURN arg1 + arg2 + arg3 + 1.23456789;
END FuncSum;",

String query = "begin ? := \"TIGER\".FuncSum('0', 1.1, ?); end;";

statement = connection.prepareCall(query);

ParameterMetaData paramsMeta = statement.getParameterMetaData();
Assert.assertEquals(2, paramsMeta.getParameterCount());
Assert.assertEquals(ParameterMetaData.parameterModeOut, paramsMeta.getParameterMode(1));
Assert.assertEquals(Types.DECIMAL, paramsMeta.getParameterType(1));
Assert.assertEquals(ParameterMetaData.parameterModeIn, paramsMeta.getParameterMode(2));
Assert.assertEquals(Types.DECIMAL, paramsMeta.getParameterType(2));

statement.setInt(2, 2);
statement.registerOutParameter(1, Types.DOUBLE);
statement.execute();

Read the result in either of the following ways:

System.out.println("Result is: " + statement.getDouble(1));

or

ResultSet rs = statement.getResultSet();
if (rs.next())
System.out.println("Result is: " + rs.getDouble(1)); 

Using Statement Objects

You can use the execute method of the Statement class to execute any stored procedure as an SQL statement.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the SearchSuppliers stored procedure:

Statement stmt = conn.createStatement();
boolean ret = stmt.execute("EXEC SearchSuppliers Country = 'US'");

if (!ret) {
  int count=stmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=stmt.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
    }
  }
}