PL/SQL Program unit call from OAF

Writing bigger program logic in controller will be bit complex, To resolve such cases write logic in PL/SQL program units and invoke this from OAF pages.In next lines I am writing the code to call pl/sql in oaf pages.


// Required import packages
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;   
import oracle.jdbc.OracleCallableStatement;
import oracle.jbo.domain.Number;
//

OAApplicationModule localOAApplicationModule = paramOAPageContext.getApplicationModule(paramOAWebBean);
OADBTransaction oadbtransaction =
(OADBTransaction)((OAApplicationModuleImpl)localOAApplicationModule).getDBTransaction();
String callSQLPackage = "begin XXXXX_CUSTOM_PACKAGE.XXXXX_CUSTOM_PROCEDURE(:1,:2,:3,:4,:5);" + " end;";
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(callSQLPackage,1);
try
{
      // Set IN Parameters               
      oraclecallablestatement.setString(1,in1Parameter);                               
      oraclecallablestatement.setString(2,in2Parameter);
      
      // Set OUT Parameters
      oraclecallablestatement.registerOutParameter(3,Types.INTEGER);             
      oraclecallablestatement.registerOutParameter(4,Types.INTEGER);             
      oraclecallablestatement.registerOutParameter(5,Types.VARCHAR,0,2000);
                 
      oraclecallablestatement.execute();
 
      // Extract the OUT parameter value
    int out1Param = oraclecallablestatement.getInt(3); 
    int out2Param = oraclecallablestatement.getInt(4);
    String out3Param = oraclecallablestatement.getString(5);
          
 }catch(SQLException sqle)
 {
    throw new OAException("Exception Block"+sqle);
 }