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

Previous post was about calling PL/SQL Procedure with OUT parameters in ADF Application
ADF Basics: Call PL/SQL Procedure with OUT parameter in ADF Application

And this post is about calling PL/SQL Function with OUT parameters. Basic difference between a Function and Procedure is that Function must return a value but procedure may or may not return a value
So there is always one OUT parameter in PL/SQL Function

Here we are talking about a function that has OUT parameters other than default one, for demo purpose I have created a PL/SQL Function  that takes EmployeeId as input parameter and return First Name and Last Name as OUT parameter and returns Employee Email Id as it's default return value (Using Oracle HR Schema)

CREAET OR REPLACE FUNCTION FN_GET_EMPNAME(EMP_NO IN NUMBER, F_NAME OUT VARCHAR2,L_NAME OUT VARCHAR2) 
RETURN VARCHAR2 IS
   EMAIL_V VARCHAR2(50) := 'N';
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;
    SELECT EMAIL INTO EMAIL_V FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;
    RETURN EMAIL_V;
END;

Code to call this Function from ADF, A little difference in syntax of calling procedure and function

    /**Method to call PL/SQL function with OUT parameters
     */
    public void callStoredFunctionOutParam() {
        CallableStatement st = null;
        try {
            //Creating sql statement
            st = this.getDBTransaction().createCallableStatement("BEGIN ? := FN_GET_EMPNAME(?,?,?);END;", 0);

            //Register out parameter for default return value of function
            st.registerOutParameter(1, Types.VARCHAR);

            //Set IN parameter's value, here 110 is EmployeeId
            st.setObject(2, 110);

            //Register other out parameters of function
            //Here I have only one input parameter so I'll start out parameter index from 3

            //1 - Default return parameter of function
            //2 - Input parameter of function

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

            st.executeUpdate();

            System.out.println("\n\n\nEMAIL- " + st.getObject(1) + " FIRST_NAME- " + st.getObject(3) + " LAST_NAME- " +
                               st.getObject(4));
        } catch (SQLException e) {
            throw new JboException(e.getMessage());

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

                }
            }
        }
    }

Output on Console

Cheers :) Happy Learning