How to Call PL/SQL from ADF?

You can use the following code to call a pl/sql block from the ADF bc Application module.


public void executePlsqlProcedure(){

CallableStatement updateStmt = null;
try {
// Put your PL/SQL block in a String variable.
String Stmt = "begin delete test_table where header_id = :1; "+
" :2 = xxx_pkg.yyy_function(); end;";
OADBTransaction txn = getDBTransaction();

// Pass the PL/SQL block to the callable Statement
updateStmt = txn.createCallableStatement(Stmt, 1);


// Set all the bind variables before calling the execute command
updateStmt.setInt(1, headerIdToDelete);

// And register the output parameter types
updateStmt.registerOutParameter(2, Types.DOUBLE);
updateStmt.executeUpdate();

// After execute you can get the value of pl/sql block output
Number amount = new Number(updateStmt.getDouble(2));
updateStmt.close();

// Commit the transaction in the database
txn.commit();


} catch(SQLException sqle) {
updateStmt.close();
}

}



And you can call the above Application Modules method from managed bean using following code.

FacesContext facesContext = FacesContext.getCurrentInstance();
ExpressionFactory exp = facesContext.getApplication().getExpressionFactory();
DCBindingContainer bindingContainer = (DCBindingContainer)exp.createValueExpression(facesContext.getELContext(),"#{bindings}",DCBindingContainer.class).getValue(facesContext.getELContext()); 
DCIteratorBinding itr = bindingContainer.findIteratorBinding("Dept1Iterator"); 
DemoAppAMImpl svc = (DemoAppAMImpl)itr.getDataControl().getApplicationModule();


Or you can call this method by exposing into client interface.