ADF Basics: Call PL/SQL Procedure with OUT parameter in ADF Application

Previously i have posted about calling pl/sql function in ADF Application

ADF Basics: Call PL/SQL Stored function in ADF Application

This post is about calling stored procedure in ADF Application , a very basic requirement.

Sometimes we need to call a procedure that has OUT parameters , these parameters are used to return value from procedure. There may be n- numbers of OUT parameters in a procedure 

In this post i am discussing same so for demo purpose I have created a PL/SQL procedure  that takes EmployeeId as input parameter and return First Name and Last Name as OUT parameter (Using Oracle HR Schema)

CREATE OR REPLACE PROCEDURE PROC_GET_EMPNAME(EMP_NO IN NUMBER, F_NAME OUT VARCHAR2,L_NAME OUT VARCHAR2) IS
BEGIN
   SELECT FIRST_NAME INTO F_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;
    SELECT LAST_NAME INTO L_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;
END;

Now see code to call procedure from ADF Application

/**Method to call Database function
     * @param stmt
     * @param bindVars
     * @return
     */
    protected void callStoredProcOut(String stmt, Object[] bindVars) {
        CallableStatement st = null;
        try {
          //Creating sql statement
            st = this.getDBTransaction().createCallableStatement("begin " + stmt + ";end;", 0);
            
          
            //  Set the bind values of the IN parameters (Employee Id is Input parameter here)
            st.setObject(1, bindVars[0]);

           //Register out parameters and their types (In this case procedure has 2 out parameters)

            st.registerOutParameter(2, Types.VARCHAR);
            st.registerOutParameter(3, Types.VARCHAR);
           

            // Execute the statement
            st.executeUpdate();
           
           // Print Return Values of out parameters
            System.out.println("First Name-" + st.getString(2) +" Last Name-"+st.getString(3) );

          
        } catch (SQLException e) {
            throw new JboException(e.getMessage());
          
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {

                }
            }
        }
    }

//Call PL/SQL procedure using this helper method
    public void callStoredProcedure() {

       //Here we will pass only input parameter but write procedure signature for all parameters (IN and OUT)
       callStoredProcOut("PROC_GET_EMPNAME(?,?,?)", new Object[] { 110 });

    }

So this is how we can use procedure with OUT parameters , you can change code according to number of OUT and IN parameters your procedure has
See output on console

Cheers :) Happy Learning