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

The method to call your PL/SQL code, created in your page or regions 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;
}

OAF White Paper

I recently presented a white paper at the 2006 South African Oracle User Group, an introduction to OAF development called: Oracle Applications Development Framework - The New Frontier

I hope this will help new comer's to get a good introduction to OAF development, extension and personalization. If there are any questions related to the white paper, please drop me a comment and I will reply a.s.a.p.

Please leave me a comment with your e-mail address and will get the document to you.

UK hear I come

First and foremost I would like to apologise for not updating the blog for the past couple of month's, come to think of it, it's my first blog for 2007.

Apart from being extremely busy building a custom bolt-on OAF application for a large multinational, I have been applying to work in the United Kingdom under the government's Highly Skilled Migrant Program (HSMP).

Well I have been accepted and am currently looking for some work before I arrive in the UK on the 1st of May, so any help would be highly appreciated ;-). I am also in the process of creating a new blog to detail my experiences living, working and playing in the UK, so check back soon for a link.

If there are any particular areas of the OAF you would like me to explore more, please drop me a comment and I will consider doing a post.