Call PL/SQL Function with OUT parameter in ADF Application

Sharing is Caring

The previous post was about calling PL/SQL Procedure with OUT parameters in ADF Application. In this post, I’ll show you how to call PL/SQL Function with OUT parameters.
ADF Basics: Call PL/SQL Procedure with OUT parameter in ADF Application

The 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 returns 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(“nnnEMAIL- “ + 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

Call PL/SQL Function

Cheers 🙂 Happy Learning

Related Posts

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

Leave a Reply

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