How to Call a PL/SQL block from OA framework

You can call PL/SQL block from the OA Framework using callable statement. Use the following code from application module.


import java.sql.CallableStatement;
import oracle.apps.fnd.common.VersionInfo; 
import java.sql.SQLException; 

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

// Pass the PL/SQL block to the callable Statement 
updateStmt = txn.createCallableStatement(deleteStmt, 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(); 
}


If you want to call pl/sql from the controller then get the application module from the pageContext.
And then get the db transaction from the application module
OADBTransaction txn = pageContext.getApplicationModule(webBean).getOADBTransaction();