Search Results


Tuesday, November 04, 2008

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
Post a Comment