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