We need to use a Callable Statement to call either a function or a procedure from OAF.
Here are the steps to call a PL/SQL procedure:
- Create a CallableStatement with the PL/SQL block of code to call the stored procedure
- Bind input and output variabled
- Execute CallableStatement
- Capture OUT parameters
- Close the CallableStatement statement
import java.sql.CallableStatement;
import java.sql.Types;
import oracle.apps.fnd.framework.server.OADBTransaction;
public void callProcedure() {
String lkpCode = "XX_VAL_LIST_LKP";
String lkpMeaning = "XX Lkp Meaning";
CallableStatement callableStatement =
getOADBTransaction().createCallableStatement("begin xx_lkp_details_pkg.list_lkp(:1,:2,:3); end;",
OADBTransaction.DEFAULT);
try {
callableStatement.setString(1, lkpCode);
callableStatement.setString(2, lkpMeaning);
//registering out parameter
callableStatement.registerOutParameter(3, Types.VARCHAR);
callableStatement.execute();
String tagValue = callableStatement.getString(3);
if (tagValue != null) {
System.out.println("Tag value: " + tagValue);
}
} catch (Exception e) {
System.out.println(" XX in Catch Block of callableStatement: " +
e.getMessage());
}
}