CallableStatement: Calling PL/SQL Procedure from OAF

By | March 18, 2015 | 7,368 views | Category: OAF

We need to use a Callable Statement to call either a function or a procedure from OAF.

Here are the steps to call a PL/SQL procedure:

  1. Create a CallableStatement with the PL/SQL block of code to call the stored procedure
  2. Bind input and output variabled
  3. Execute CallableStatement
  4. Capture OUT parameters
  5. Close the CallableStatement statement
    import java.sql.CallableStatement;
    import java.sql.Types;
    import oracle.apps.fnd.framework.server.OADBTransaction;
    public void callProcedure() {
        String lkpCode = "XX_VAL_LIST_LKP";
        String lkpMeaning = "XX Lkp Meaning";
        CallableStatement callableStatement = 
            getOADBTransaction().createCallableStatement("begin  xx_lkp_details_pkg.list_lkp(:1,:2,:3); end;", 
        try {
            callableStatement.setString(1, lkpCode);
            callableStatement.setString(2, lkpMeaning);
            //registering out parameter
            callableStatement.registerOutParameter(3, Types.VARCHAR);
            String tagValue = callableStatement.getString(3);
            if (tagValue != null) {
                System.out.println("Tag value: " + tagValue);
        } catch (Exception e) {
            System.out.println(" XX in Catch Block of callableStatement: " + 
Category: OAF
WOW! Did you like this post? We'll send more interesting posts like CallableStatement: Calling PL/SQL Procedure from OAF to you!
Enter your Email Address: