Calling PL/SQL from OAF Application Module

Note: probably the most critical part is getOADBTransaction().createCallableStatement(s, -1). take note of the CallableStatment object - this might have a generic implementation on Java or other frameworks.

primary method (inside AMImpl.java)

    public void searchRequiredDocuments (String loanTypeCode) {

       

        if (getXxhlsRequiredDocumentsVO1().getRowCount() == 0) {

           

            OAViewObject vo = getXxhlsRequiredDocumentsVO1();

            CallableStatement cs = null;

            ResultSet rs = null;

            String s = "BEGIN ? := xxhls_test_pkg.get_actv_loan_docs_applied_fn(?); END;";

            try {

                cs = getOADBTransaction().createCallableStatement(s, -1);

                cs.registerOutParameter(1, OracleTypes.CURSOR);

                cs.setString(2, loanTypeCode);

                cs.execute();

                rs = ((OracleCallableStatement)cs).getCursor(1);

                populateViewObject(rs, vo);

            } catch (Exception sqlexception) {

                throw new OAException(sqlexception.getMessage());

            } finally {

                try {

                    if (cs != null)

                        cs.close();

                    if (rs != null)

                        rs.close();

                } catch (SQLException sqlexception1) {

                    throw new OAException(sqlexception1.getMessage());

                }

               

            }

           

        }

       

    }

utility methods

    protected static void populateViewObject (ResultSet rs, OAViewObject vo) throws SQLException {

        Map rsvo = new java.util.HashMap();

        if (vo == null) {

            return;

        }

        rsvo = mapAttrNames(vo);

        int cnt = 0;

        while (rs.next()) {

            Row row = vo.createRow();

            vo.setRangeSize(cnt + 1);

            vo.insertRowAtRangeIndex(cnt, row);

            ResultSetMetaData rsmd = rs.getMetaData();

            int numOfCol = rsmd.getColumnCount();

            for (int i = 0; i < numOfCol; i++) {

                String colName = rsmd.getColumnName(i + 1);

                String attrName = (String)rsvo.get(colName);

                if (attrName != null)

                    setVoAttribute(vo, row, attrName, rs.getObject(colName));

            }

            cnt++;

        }

    }

    protected static Map mapAttrNames (OAViewObject vo) {

        Map attrNames = new java.util.HashMap();

        if (vo == null) {

            return null;

        }

        AttributeDef[] attrDefs = vo.getAttributeDefs();

        int attrCount = (attrDefs == null) ? 0 : attrDefs.length;

        if (attrCount == 0) {

            return null;

        }

        for (int i = 0; i < attrCount; i++) {

            byte attrKind = attrDefs[i].getAttributeKind();

            if (!(attrKind == AttributeDef.ATTR_ASSOCIATED_ROW || attrKind == AttributeDef.ATTR_ASSOCIATED_ROWITERATOR || attrKind == AttributeDef.ATTR_DYNAMIC)) {

                String colName = attrDefs[i].getColumnNameForQuery();

                String attrName = attrDefs[i].getName();

                attrNames.put(colName, attrName);

            }

        }

        return attrNames;

    }

    protected static void setVoAttribute (OAViewObject vo, Row row, String attrName, Object attrVal) {

        if (vo.lookupAttributeDef(attrName) != null) {

            if (attrVal != null) {

                row.setAttribute(attrName, attrVal);

            }

        }

    }

Another way I found on Andries blog . I think I like this because it's much simpler; but you need to manually populate the View Objects

1. calling from CO

// Invoke the method

HashMap returnValues = (HashMap) am.invokeMethod("executePlsql");

// Get the returned values from the method

String status = (String) returnValues.get("Status");

String message = (String) returnValues.get("Message");

if (!status.equals("SUCCESS")) {

  am.invokeMethod("rollback");

  // ERRORS from executePlsql

  MessageToken[] tokens =

    {new MessageToken("STATUS", status),

     new MessageToken("MESSAGE", message)};

  OAException errorMessage = new OAException("XX",

  "XX_PLSQL_ERR", tokens,

  OAException.ERROR, null);

  pageContext.putDialogMessage(errorMessage);

}

2. calling from AM

import com.sun.java.util.collections.HashMap;

import java.sql.CallableStatement;

import java.sql.SQLException;

import java.sql.Types;

public HashMap executePlsql(String objectId, String objectSubType) {

  CallableStatement st = null;

  HashMap returnValues = new HashMap(2);

  try {

    String stmt = "BEGIN xx_pkg.xx_procedure( "

      + "p_object_id => :1, "

      + "p_object_sub_type => :2, "

      + "p_status => :3, "

      + "p_message => :4); END;";

    OADBTransaction tr = getOADBTransaction();

    st = tr.createCallableStatement(stmt, 1);

    // Bind the input parameters

    st.setInt(1, Integer.parseInt(objectId));

    st.setString(2, objectSubType);

    // Register return variables

    st.registerOutParameter(3, Types.VARCHAR);

    st.registerOutParameter(4, Types.VARCHAR);

    st.execute();

    // Assign returned values to variables

    String status = st.getString(3);

    String message = st.getString(4);

    st.close();

    // Populate HashMap with return variables

    returnValues.put("Status", status);

    returnValues.put("Message", message);

    OAExceptionUtils.checkErrors(tr);

  }

  catch (SQLException sqle) {

    throw OAException.wrapperException(sqle);

  }

  return returnValues;

}