25.5 Invoking Stored Procedures and Functions

You can write code in the custom Java classes for your business components to invoke database stored procedures and functions. Here you'll consider some simple examples based on procedures and functions in a PL/SQL package; however, using the same techniques, you also can invoke procedures and functions that are not part of a package.

Consider the following PL/SQL package:

create or replace package devguidepkg as
  procedure proc_with_no_args;
  procedure proc_with_three_args(n number, d date, v varchar2);
  function  func_with_three_args(n number, d date, v varchar2) return varchar2;
  procedure proc_with_out_args(n number, d out date, v in out varchar2);
end devguidepkg;

The following sections explain how to invoke each of the example procedures and functions in this package.

Note:

The examples in this section refer to the StoredProcedureInvocation project in the AdvancedExamples workspace. See the note at the beginning of this chapter for download instructions.

25.5.1 Invoking Stored Procedures with No Arguments

If you need to invoke a stored procedure that takes no arguments, you can use the executeCommand() method on the DBTransaction interface (in the oracle.jbo.server package as shown in Example 25-6.

Example 25-6 Executing a Stored Procedure with No Arguments

// In StoredProcTestModuleImpl.java
public void callProcWithNoArgs() {
  getDBTransaction().executeCommand(
    "begin devguidepkg.proc_with_no_args; end;");
}

25.5.2 Invoking Stored Procedure with Only IN Arguments

Invoking stored procedures that accept only IN-mode arguments — which is the default PL/SQL parameter mode if not specified — requires using a JDBC PreparedStatement object. The DBTransaction interface provides a createPreparedStatement() method to create this object for you in the context of the current database connection. You could use a helper method like the one shown in Example 25-7 to simplify the job of invoking a stored procedure of this kind using a PreparedStatement. Importantly, by using a helper method, you can encapsulate the code that closes the JDBC PreparedStatement after executing it. The code performs the following basic tasks:

Creates a JDBC PreparedStatement for the statement passed in, wrapping it in a PL/SQL begin...end block.
Loops over values for the bind variables passed in, if any.
Sets the value of each bind variable in the statement.

Notice that since JDBC bind variable API's use one-based numbering, the code adds one to the zero-based for loop index variable to account for this.
Executes the statement.
Closes the statement.

Example 25-7 Helper Method to Simplify Invoking Stored Procedures with Only IN Arguments

protected void callStoredProcedure(String stmt, Object[] bindVars) {
  PreparedStatement st = null;
  try {
    // 1. Create a JDBC PreparedStatement for 
    st = getDBTransaction().createPreparedStatement("begin "+stmt+";end;",0);
    if (bindVars != null) {
      // 2. Loop over values for the bind variables passed in, if any
      for (int z = 0; z < bindVars.length; z++) {
        // 3. Set the value of each bind variable in the statement
        st.setObject(z + 1, bindVars[z]);
      }
    }
    // 4. Execute the statement
    st.executeUpdate();
  }
  catch (SQLException e) {
    throw new JboException(e);
  }
  finally {
    if (st != null) {
      try {
        // 5. Close the statement
        st.close();
      }
      catch (SQLException e) {}
    }
  }
}

With a helper method like this in place, calling the proc_with_three_args procedure above would look like this:

// In StoredProcTestModuleImpl.java
public void callProcWithThreeArgs(Number n, Date d, String v) {
  callStoredProcedure("devguidepkg.proc_with_three_args(?,?,?)",
                      new Object[]{n,d,v});
}

Notice the question marks used as JDBC bind variable placeholders for the arguments passed to the function. JDBC also supports using named bind variables, but using these simpler positional bind variables is also fine since the helper method is just setting the bind variable values positionally.

25.5.3 Invoking Stored Function with Only IN Arguments

Invoking stored functions that accept only IN-mode arguments requires using a JDBC CallableStatement object in order to access the value of the function result after executing the statement. The DBTransaction interface provides a createCallableStatement() method to create this object for you in the context of the current database connection. You could use a helper method like the one shown in Example 25-8 to simplify the job of invoking a stored function of this kind using a CallableStatement. As above, the helper method encapsulates both the creation and clean up of the JDBC statement being used.

The code performs the following basic tasks:

Creates a JDBC CallableStatement for the statement passed in, wrapping it in a PL/SQL begin...end block.
Registers the first bind variable for the function return value.
Loops over values for the bind variables passed in, if any.
Sets the value of each bind user-supplied bind variable in the statement.

Notice that since JDBC bind variable API's use one-based numbering, and since the function return value is already the first bind variable in the statement, the code adds two to the zero-based for loop index variable to account for these.
Executes the statement.
Returns the value of the first bind variable.
Closes the statement.

Example 25-8 Helper Method to Simplify Invoking Stored Functions with Only IN Arguments

// Some constants
public static int NUMBER = Types.NUMERIC;
public static int DATE = Types.DATE;
public static int VARCHAR2 = Types.VARCHAR;

protected Object callStoredFunction(int sqlReturnType, String stmt,
                                    Object[] bindVars) {
  CallableStatement st = null;
  try {
    // 1. Create a JDBC CallabledStatement  
    st = getDBTransaction().createCallableStatement(
           "begin ? := "+stmt+";end;",0);
    // 2. Register the first bind variable for the return value
    st.registerOutParameter(1, sqlReturnType);
    if (bindVars != null) {
      // 3. Loop over values for the bind variables passed in, if any
      for (int z = 0; z < bindVars.length; z++) {
        // 4. Set the value of user-supplied bind vars in the stmt
        st.setObject(z + 2, bindVars[z]);
      }
    }
    // 5. Set the value of user-supplied bind vars in the stmt
    st.executeUpdate();
    // 6. Return the value of the first bind variable
    return st.getObject(1);
  }
  catch (SQLException e) {
    throw new JboException(e);
  }
  finally {
    if (st != null) {
      try {
        // 7. Close the statement
        st.close();
      }
      catch (SQLException e) {}
    }
  }
}

With a helper method like this in place, calling the func_with_three_args procedure above would look like this:

// In StoredProcTestModuleImpl.java
public String callFuncWithThreeArgs(Number n, Date d, String v) {
  return (String)callStoredFunction(VARCHAR2,
                            "devguidepkg.func_with_three_args(?,?,?)",
                            new Object[]{n,d,v});
}

Notice the question marks as above that are used as JDBC bind variable placeholders for the arguments passed to the function. JDBC also supports using named bind variables, but using these simpler positional bind variables is also fine since the helper method is just setting the bind variable values positionally.

25.5.4 Calling Other Types of Stored Procedures

Calling a stored procedure or function like devguidepkg.proc_with_out_args that includes arguments of OUT or IN OUT mode requires using a CallableStatement as in the previous section, but is a little more challenging to generalize into a helper method. Example 25-9 illustrates the JDBC code necessary to invoke the devguidepkg.proc_with_out_args procedure.

The code performs the following basic tasks:

Defines a PL/SQL block for the statement to invoke.
Creates the CallableStatement for the PL/SQL block.
Registers the positions and types of the OUT parameters.
Sets the bind values of the IN parameters.
Executes the statement.
Creates a JavaBean to hold the multiple return values

The DateAndStringBean class contains bean properties named dateVal and stringVal.
Sets the value of its dateVal property using the first OUT param.
Sets value of its stringVal property using second OUT param.
Returns the result.
Closes the JDBC CallableStatement.

Example 25-9 Calling a Stored Procedure with Multiple OUT Arguments

public Date callProcWithOutArgs(Number n, String v) {
  CallableStatement st = null;
  try  {
    // 1. Define the PL/SQL block for the statement to invoke
    String stmt = "begin devguidepkg.proc_with_out_args(?,?,?); end;";
    // 2. Create the CallableStatement for the PL/SQL block
    st = getDBTransaction().createCallableStatement(stmt,0);
    // 3. Register the positions and types of the OUT parameters
    st.registerOutParameter(2,Types.DATE);
    st.registerOutParameter(3,Types.VARCHAR);
    // 4. Set the bind values of the IN parameters
    st.setObject(1,n);
    st.setObject(3,v);
    // 5. Execute the statement
    st.executeUpdate();
    // 6. Create a bean to hold the multiple return values
    DateAndStringBean result = new DateAndStringBean();
    // 7. Set value of dateValue property using first OUT param
    result.setDateVal(new Date(st.getDate(2)));
    // 8. Set value of stringValue property using 2nd OUT param
    result.setStringVal(st.getString(3));
    // 9. Return the result
    return result;
  } catch (SQLException e)  {
    throw new JboException(e);
  } finally  {
    if (st != null) {
      try {
        // 10. Close the JDBC CallableStatement
        st.close();
      }
      catch (SQLException e) {}
    }    
  }
}

The DateAndString bean used in Example 25-9 is a simple JavaBean with two bean properties like this:

package devguide.advanced.storedproc;
import java.io.Serializable;
import oracle.jbo.domain.Date;
public class DateAndStringBean implements Serializable {
  Date dateVal;
  String stringVal;
  public void setDateVal(Date dateVal) {this.dateVal=dateVal;}
  public Date getDateVal() {return dateVal;}
  public void setStringVal(String stringVal) {this.stringVal=stringVal;}
  public String getStringVal() {return stringVal;}
}

Note:

In order to allow the custom method to be a legal candidate for inclusion in an application module's custom service interface (if desired), the bean needs to implement the java.io.Serializable. interface. Since this is a "marker" interface, this involves simply adding the implements Serializable keywords without needing to code the implementation of any interface methods.