Call PL/SQL Procedure with OUT parameter in ADF Application

Sharing is Caring
Previously I have posted about calling PL/SQL function in ADF Application, In this post I’ll show you how to Call PL/SQL Procedure in Oracle ADF Application.
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

Call PL/SQL Procedure

Cheers 🙂 Happy Learning

Related Posts

An Oracle ACE, Blogger, Reviewer, Technical Lead working on Oracle ADF

2 thoughts on “Call PL/SQL Procedure with OUT parameter in ADF Application”

  1. Dear Ashish,

    A very helpful post.
    I have written the procedure “callStoredProcOut” in AppModuleImpl.java file and now when i call it in my bean .java file it cannot find it.

    It is also not available to register it in Client Interface in ApplModule.xml

    Regards,
    Zahid Rahim

Leave a Reply

Your email address will not be published. Required fields are marked *