How to invoke Stored Procedure(Pl/SQL) using ADF

This blog explains about invoking PL/SQL Stored procedure using ADF

       

    /**
     *
     * @param pDBTransaction - Instance of DBTransaction
     * @param stmt - PL/SQL Stored Procedure Name
     * @param bindVars - Array of Bind variables if needed.
     */
    public static void invokeStoredProcedure(DBTransaction pDBTransaction,
                                           String stmt, Object[] bindVars)
    {
        PreparedStatement pst = null;
        try{
            // Create Prepared statement using DBTransaction
            pst =
                pDBTransaction.createPreparedStatement("begin " + stmt + ";end;",
                                                       0);
            // Set Bind variables
            if (bindVars != null)
            {
              for (int z = 0; z < bindVars.length; z++)
              {
                pst.setObject(z + 1, bindVars[z]);
              }
            }    
            // Invoke Stored Procedure
            pst.executeUpdate();
        }catch(Exception ex){
            ex.printStackTrace();
            }finally{
            if(pst != null){
                try {
                    pst.close();
                } catch (SQLException sqle) {
                    sqle.printStackTrace();
                }
            }
            }
    }
       

- Below is sample code to invoke Stored Function

       

    /**
     * This method invokes Stored Function based on given input params
     * @param sqlReturnType
     * @param stmt
     * @param bindVars
     * @return
     */
    private Object callStoredFunction(int sqlReturnType, String storedProcedureStmt,
                                        Object[] bindVars) {
        CallableStatement st = null;
        try {
            // 1. Create a JDBC CallabledStatement
            st =
 getDBTransaction().createCallableStatement("begin ? := " + storedProcedureStmt + ";end;", 0);
            // 2. Register the first bind variable for the return value
            st.registerOutParameter(1, sqlReturnType);
            if (bindVars != null) {
                // 3. Loop over values for the bind variables passed in, if any
                for (int z = 0; z < bindVars.length; z++) {
                    // 4. Set the value of user-supplied bind vars in the stmt
                    st.setObject(z + 2, bindVars[z]);
                }
            }
            // 5. Set the value of user-supplied bind vars in the stmt
            st.executeUpdate();
            // 6. Return the value of the first bind variable
            return st.getObject(1);
        } catch (SQLException e) {
            throw new JboException(e);
        } finally {
            if (st != null) {
                try {
                    // 7. Close the statement
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public String getEmpFullName(Number n) {
        return (String)callStoredFunction(1, "get_emp_fullname(?)",
                                          new Object[] {100 });
    }
 
 // Below is Stored Function
 
 create or replace function get_emp_fullname (empid in number) return varchar2 
as
 v_full_name varchar2(120);
begin
  select first_name||' '||last_name
   into v_full_name
   from employees
   where employee_id = empid;
    
  return v_full_name;
exception
 when no_data_found then 
   return null;
end get_emp_fullname;