Monday, 26 March 2007

Call PL/SQL from OAF

In many situations developers need to execute PL/SQL code from there OAF application, be it legacy code or complex database manipulation better suited for PL/SQL.

The following example calls a PL/SQL procedure, passing in and receiving back parameter values. To ensure we athere to the MVC architecture, you must execute this code from your Application Module (AM). The method returns a HashMap object containing the two return values from the procedure, the HashMap can be returned to your controller, and used to raise a message for the user.

The following code is placed in your region or page controller, to call the method from your AM:

// Invoke the method
HashMap returnValues = (HashMap) am.invokeMethod(

// Get the returned values from the method
String status = (String) returnValues.get("Status");
String message = (String) returnValues.get("Message");

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

// 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);


The method to call your PL/SQL code, created in your page or regions AM:

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

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


// Assign returned values to variables
String status = st.getString(3);
String message = st.getString(4);


// Populate HashMap with return variables
returnValues.put("Status", status);
returnValues.put("Message", message);

catch (SQLException sqle)
throw OAException.wrapperException(sqle);

return returnValues;

No comments: