Stored Procedures in ADF

One of the common requirements for ADF programmers is to invoke Stored Procedures and Functions. Implementing this is so simple with few lines of code in Java classes used under business components. Following code snippets from fusion guide will help execute different procedures with and without arguments.

Execute stored procedure with No Arguments:

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

Execute stored procedure with only IN arguments:

Procedures often take arguments in order to process some business logic. Arguments are limited from none to many. In order to use stored procedures with argument mode we need to use JDBC PreparedStatement object.

How To:

1. Create connection
2. Create a JDBC PreparedStatement with procedure details wrapped in a PL/SQL begin..end block.
3. Assign the values to arguments if any.
4. Execute the statement.
5. Close the statement.

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) {}
    }
  }
}

You can easily call the procedure anywhere using few lines as shown below:

 callStoredProcedure("devguidepkg.proc_with_three_args(?,?,?)",

                      new Object[]{n,d,v}); //Where n,d,v are the args for procedure

Execute stored function with only IN arguments (returns result):

It's quite often to have procedures which returns some result which might be needed for further processing or display to user. JDBC CallableStatement is needed in order to access the value of the function result after executing the statement. CallableStatement object for the context of current database connection can be created using createCallableStatement() method of DBTransaction interface.

How To:

1. Create a JDBC CallableStatement.
2. Registers the first bind variable for the function return value.
3. Loops over values for the bind variables and set user supplied values to arguments.
4. Execute the statement.
5. Return the value of first bind variable(result).
6. Close statement.

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) {}
    }
  }
}

Call it anywhere as below:

 return (String)callStoredFunction(VARCHAR2,
                            "devguidepkg.func_with_three_args(?,?,?)",
                            new Object[]{n,d,v});

Executing a Stored Procedure with multiple OUT arguments:

1. Define statement to invoke.
2. Create CallableStatement.
3. Registers the positions and types of the OUT params.
4. Sets the bind values of the IN params.
5. Execute the statement.
6. Get the result
7. Close CallableStatement

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) {}
    }    
  }
}   

A Bean can be created to manage types and return values as below:

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;}
}

Notice the '?' placeholders in all the above methods. So it is developers responsibility to send & receive the IN and OUT params in order.

Also procedures can be invoked calling JDBC CallableStatement using DBTransactionImpl object, which avoids few lines but pretty much does the same thing as shown below:

            Connection con;
            DBTransactionImpl dbti = (DBTransactionImpl)getDBTransaction();
            CallableStatement statement =
                dbti.createCallableStatement(("BEGIN " +
                                              "PROC_PKG.CREATE_EMP(?,?); " +
                                              "END;"), 0);
            try {
                con = getDBTransaction().createStatement(1).getConnection();
                ArrayDescriptor des =
                    ArrayDescriptor.createDescriptor("ARRAY_TABLE", con);
                String dIsArr[] = documentIds.split(",");
                ARRAY array_to_pass = new ARRAY(des, con, dIsArr);

                statement.setArray(1, array_to_pass);
                statement.setString(2, EMP_NAME);
                statement.setInt(3, EMP_AGE);
                statement.execute();
                statement.close();


References: ADF dev guide

Thanks. Happy Learning :)