Call PL/SQL Stored function in ADF Application

Sharing is Caring

This post is about calling a PL/SQL stored function in ADF Application, a very basic requirement. Many times we need to call a PL/SQL function in the ADF app for any specific requirement.
In this post, I am discussing same so for that, I have created a PL/SQL function that takes EmployeeId as input parameter and returns its Employees Name (Using Oracle HR Schema)

CREATE OR REPLACE FUNCTION FN_GET_EMPNAME(EMP_NO NUMBER)
RETURN VARCHAR2 IS
   EMP_NAME VARCHAR2(50) := 'N';
BEGIN
   SELECT FIRST_NAME||' '||LAST_NAME into EMP_NAME
   FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;

RETURN EMP_NAME;
END;

I have a ready to use a helper method to call PL/SQL function, Check it

import java.sql.CallableStatement;


import java.sql.SQLException;

import java.sql.Types;

import oracle.jbo.JboException;

/**Method to call Database function
 * @param sqlReturnType (Return type of Function)
 * @param stmt (Function Name with Parameters)
 * @param bindVars (Parameter's Value)
 * @return
 */
protected Object callStoredFunction(int sqlReturnType, String stmt, Object[] bindVars) {
CallableStatement cst = null;
try {
//Creating sql statement
cst = this.getDBTransaction().createCallableStatement("begin ? := " + stmt + ";end;", 0);
//Register dataType for return value
cst.registerOutParameter(1, sqlReturnType);
//Pass input parameters value
if (bindVars != null) {
for (int z = 0; z < bindVars.length; z++) {
cst.setObject(z + 2, bindVars[z]);
}
}
cst.executeUpdate();
//Finally get returned value
return cst.getObject(1);
} catch (SQLException e) {
throw new JboException(e.getMessage());
} finally {
if (cst != null) {
try {
cst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

It’s simple, now see how to use this helper method to call PL/SQL function, Created a method in Application Module Impl class

    /**Method to call stored PL/SQl function to get Employee Name
     * @param empId
     * @return
     */
    public String getEmployeeName(Integer empId) {
        String empNm = "No Employee found";
        Object empName = callStoredFunction(Types.VARCHAR, "FN_GET_EMPNAME(?)", new Object[] { empId });
        if (empName != null) {
            empNm = empName.toString();
        }
        return empNm;
    }

To Check this method in AM Tester, add the method to the client interface

PL/SQL Stored function

Right click on ApplicationModule and choose Run

Put any employee id and click on execute to see the result

PL/SQL Stored function

All done:), To call this method from  managed bean you can add methodAction to page bindings and then call using Operation Binding
For more detail check – ADF Basics: How to invoke model layer methods from the managed bean (Best Practice to write business logic in ADF) 

Cheers:) Happy Learning

Related Posts

0 thoughts on “Call PL/SQL Stored function in ADF Application”

Leave a Reply

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