Call Stored Database Function in ADF

Sometimes we need to call stored database function in ADF application.

Note :- Try to call stored database function in ApplicationModuleImpl Class or in ViewObjectImpl.

Use below method to call stored function.

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 st = null;
        try {
      //Creating sql statement
            st = this.getDBTransaction().createCallableStatement("begin ? := " + stmt + ";end;", 0);

      //Register dataType for return value
            st.registerOutParameter(1, sqlReturnType);
     //Pass input parameters value
            if (bindVars != null) {

                for (int z = 0; z < bindVars.length; z++) {

                    st.setObject(z + 2, bindVars[z]);

                }
            }

            st.executeUpdate();
      //Finally get returned value
            return st.getObject(1);
        } catch (SQLException e) {
            throw new JboException(e.getMessage());
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                      e.printStackTrace();
                }
            }
        }
    }

Use above callStoredFunction method in a method in Impl class and pass parameter.

public String callFunction(Integer EmployeeId){

    String EmpNm =null;



    Object obj = callStoredFunction(Types.VARCHAR, "fn_getEmpNm(?)", new Object[] {  EmployeeId     });


    if(obj!= null) {
      EmpNm =obj.toString();
    }


    return EmpNm ;
}