Calling PL/SQL Function From OAF Page

 
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;

String outParamValue = null;
OADBTransaction txn = getDBTransaction();
CallableStatement cs = txn.createCallableStatement ("begin :1 := xx_pkg.xx_procedure(:2); end;");
try
{
// Register return variables
((OracleCallableStatement)cs.registerOutParameter(1, Types.VARCHAR, 0, 2000);

// Bind the input parameters
cs.setString(1, "gyan");
// Assign returned values to variables
outParamValue = cs.getString(1);
cs.execute();
cs.close();
}
catch (SQLException sqle){
cs.close();
}

Calling PL/SQL Procedures From OAF Page

To invoke a stored procedure from within an entity object or an application module, you need to:
1. Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
2. Bind any variables.
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters.
5. Close the statement.

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import oracle.apps.fnd.framework.server.OADBTransactionImpl;
String outParamValue = null;

OADBTransaction txn = getDBTransaction();
CallableStatement cs = txn.createCallableStatement("begin xx_pkg.xx_procedure(:1, :2); end;");
{
((OracleCallableStatement)cs.registerOutParameter(2, Types.VARCHAR, 0, 2000);
cs.setString(1, "gyan");
outParamValue = cs.getString(1);
cs.execute();
cs.close();
}
catch (SQLException sqle)
{
cs.close();

}