Call PLSQL in OAF

Calling PL/SQL Functions and Procedures

Even when writing Java entity objects, you might need to call PL/SQL functions or procedures.
Note: Do not use JDBC to perform simple SQL statements. Always leverage view objects for this purpose. If possible, you should define the view object declaratively.
In general, to invoke a stored procedure from within an entity object or an application module, you need to:
Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
Bind any variables.
Execute the statement.
Optionally retrieve the values of any OUT parameters.
Close the statement.
The following application module example shows how to create and use a CallableStatement in an entity object.

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;

...

OADBTransaction txn = getDBTransaction();
CallableStatement cs =
txn.createCallableStatement("begin dbms_application_info.set_module(:1, :2); end;"); 

try
{
cs.setString(1, module);
cs.setString(2, action);
cs.execute();
cs.close();
}
catch (SQLException sqle)
{
try { cs.close } catch (Exception(e) {}
throw OAException.wrapperException(sqle);
}

This example illustrates an OUT parameter:

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.driver.OracleCallableStatement;

...

DBTransaction txn = getDBTransaction();
String sql = "BEGIN :1 := FND_MESSAGE.GET; END;";
CallableStatement cs = txn.createCallableStatement(sql, 1);

String messageBuffer = "";

try
{
((OracleCallableStatement)cs.registerOutParameter(1, Types.VARCHAR, 0, 2000);
cs.execute();
messageBuffer = cs.getString(1);
cs.close();
}
catch (SQLException sqle)
{
try { cs.close } catch (Exception(e) {}
throw OAException.wrapperException(sqle);
}

end.