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 the 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 returns 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 the code to call a 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 the procedure with OUT parameters, you can change code according to the number of OUT and IN parameters your procedure has.

See output on the 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 *