ADF Basics: Call PL/SQL Stored function in ADF Application

This post is about calling stored function in ADF Application , a very basic requirement. Many times we need to call a PL/SQL function in 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 return it's 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 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 method to client interface

Right click on ApplicationModule and choose Run

Put any employee id and click on execute to see result

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 managed bean (Best Practice to write business logic in ADF) 

Cheers:) Happy Learning