Callable Statement

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();


Article By:
Prasanna Jayaraman

Please post your Ideas and Comments