In most of the ADF applications we have a requirement to execute a procedure from manged bean adn get the result. For this we need to follow the following steps.
Step1. Create a method in Application Module Impl class. like executeProcedure(......)
public HashMap CallProcedure(String procedureName, int noOfIPParameters, int noOfOPParameters,
ArrayList inputValues) {
HashMap<Integer,Object> hm = new HashMap<Integer,Object>();
CallableStatement cst = null;
try {
String proc = "begin ";
if (noOfIPParameters > 0) {
proc = proc + procedureName + "(";
int totalParams = noOfIPParameters + noOfOPParameters;
for (int params = 1; params <= totalParams; params++) {
if (params == totalParams) {
proc = proc + "?";
} else {
proc = proc + "?,";
}
}
}else if(noOfIPParameters==0){
proc = proc + procedureName + "(";
int totalParams = noOfOPParameters;
for (int params = 1; params <= totalParams; params++) {
if (params == totalParams) {
proc = proc + "?";
} else {
proc = proc + "?,";
}
}
}
else {
proc = proc + procedureName;
}
proc = proc + ")";
proc = proc + "; end;";
System.out.println("proc::::" + proc);
cst = getDBTransaction().createCallableStatement(proc, 0);
if (noOfOPParameters > 0) {
int outputStartVar = noOfIPParameters + 1;
for (int outputParam = outputStartVar; outputParam <= (noOfIPParameters + noOfOPParameters);
outputParam++) {
cst.registerOutParameter(outputParam, Types.VARCHAR);
}
}
if (noOfIPParameters > 0) {
for (int inputParam = 1; inputParam <= noOfIPParameters; inputParam++) {
int tempVar = inputParam - 1;
cst.setObject(inputParam, inputValues.get(tempVar));
}
}
// cst.registerOutParameter(2, Types.INTEGER);
// cst.registerOutParameter(3, Types.VARCHAR);
// cst.setInt(1, 2);
cst.execute();
// System.out.println(".............2.:::" + cst.getObject(2));
if(noOfOPParameters > 0){
int outputStartVar = noOfIPParameters + 1;
for (int outputParam = outputStartVar; outputParam <= (noOfIPParameters + noOfOPParameters);
outputParam++) {
hm.put(outputParam, cst.getObject(outputParam));
}
}
//Finally get returned value
return hm;
} catch (SQLException e) {
System.out.println("error::" + e.getMessage());
} finally {
if (cst != null) {
try {
cst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return hm;
}
Step2. Expose this method to the client interface.
For this go to java tab of application module and add in client interface like this.
Step3. Add this method as Method Action on page bindings.
Step4. By using get Operation Binding get the method action.
OperationBinding operationBinding = bindings.getOperationBinding("CallProcedure");
Step5. Execute this operation Binding and get the result.
String procedureName = "Procrdure_Package_Name.Procedure_Name";
if input parameters 0 and out parameters 1 then. ArrayList inputValues = new ArrayList();
if input paramets 2 and out parameters 2 then ArrayList inputValues = new ArrayList();
inputValues.add("in parameter1");
inputValues.add("in parameter2");
operationBinding.getParamsMap().put("procedureName", procedureName);
operationBinding.getParamsMap().put("noOfIPParameters", 0);
operationBinding.getParamsMap().put("noOfOPParameters", 1);
operationBinding.getParamsMap().put("inputValues", inputValues);
operationBinding.execute();
Object result = operationBinding.getResult();
if(result!=null){
HashMap returnValues = (HashMap) result;
}
Step6. Process the result(like to store in table or to set to some view attribute).
Generic method implementation in Application Module Impl.
