Invoke Database Package/ Procedure from OAF

Introduction

Today we will learn how to invoke Database Package/ Procedure from OAF. There are scenarios when we have to perform huge amount of tasks in Database while user interacts with the UI application. If the OAF application does the Database related tasks every now and then; then the context switching will be more resulting into slow performance of the application as it increases both Time and Space Complexity. The better approach is to create a Database Package/ Procedure and write the entire logic in small modules/ routines and invoke from OAF. Moreover some features can easily be achieved through PL/SQL API but not OAF. To call Package/ Procedure, OAF provides a standard API to invoke Database Package/ Procedure.

oracle.jdbc.OracleCallableStatement

To continue with our project (all previous blogs needs to be followed sequentially), we will calculate the age of an employee as on Date of Joining, (DOJ) i.e System Date/ Current Date with respect to Date of Birth (DOB) being entered.

Implementation

1. A Database Procedure has been created which will take DOB as an input parameter and age as as output parameter. The Procedure will calculate the age byA�DOJ – DOB and return.Here is the procedure:CREATE OR REPLACE PROCEDURE APPS.proc_calc_age(
   p_dob IN VARCHAR2
   , x_age OUT NUMBER
   )
   IS
   ln_age NUMBER;
   BEGIN
   SELECT TRUNC((sysdate – to_date(p_dob,’RRRR-MM-DD’))/365)
   INTO ln_age
   FROM dual;x_age := ln_age;
   EXCEPTION
   WHEN OTHERS
   THEN
   x_age := -1;
   END proc_calc_age;
   /
2. A methodA�calculateAge() has been written inside XXRDRootAMImpl to invoke the procedure created at Step 1 and get the age of the employee. The code snippet is given below:public int calculateAge(){
   int age = 0;
   XXRDEmpVOImpl empVO = getXXRDEmpVO1();
   Row row = empVO.getCurrentRow();
   System.out.println(“date=”+row.getAttribute(“Dob”));if(row.getAttribute(“Dob”)!=null){
   oracle.jbo.domain.Date dobDate = (oracle.jbo.domain.Date)empVO.getCurrentRow().getAttribute(“Dob”);
   String calcAgeAPIStmt = “BEGIN xxtest_calc_age(p_dob => :1 , x_age => :2 ); END;”;
   OracleCallableStatement apiCallableStmt = (OracleCallableStatement)getOADBTransaction().createCallableStatement(calcAgeAPIStmt,1);
   try{
   apiCallableStmt.setString(1,dobDate.toString());
   apiCallableStmt.registerOutParameter(2,Types.INTEGER);
   apiCallableStmt.execute();
   age = apiCallableStmt.getInt(2);
   System.out.println(“age=”+age);
   }
   catch(Exception e){
   System.out.println(“Exception Block”+e.getMessage());
   age= -2;
   }
   finally{
   try{
   apiCallableStmt.close();
   }
   catch(Exception e){
   System.out.println(“Exception Block in FInally”+e.getMessage());
   age = -3;
   }
   }
   }
   return age;
   }To explain the code written above, First need to get the DOB of the concerned employee. Create the API calling statement as similar to calling to Package/ Procedure from Anonymous Block in PL/SQL. A CallableStatement is created for the same statement with preFetchRowCount as 1. Having done that, all the IN parameters and OUT parameters is set via setXXX() method and registerOutParameter() followed by executing the CallableStatement. Upon execution, OUT parameters are retrieved. The entire process of connecting to Database, executing Package/ Procdure along with passing input parameters and fetching the output parameters is put under the try-catch block in order to handle exception. And finally the connection for CallableStatement is closed.
3. Having calculate the age, set the Age attribute of XXRDEmpVO.public void setAge(){
   XXRDEmpVOImpl empVO = getXXRDEmpVO1();
   int age = calculateAge(); // call method calculateAge()
   empVO.getCurrentRow().setAttribute(“Age”,age);
   }

Here is the output:

The invocation of Database Package/ Procedure is pretty simple and straight forward. One doesn’t have to memorize these lines of codes, the same template can be referred during usage. Hope this blog helps. In our next blog we will see how to implement LOV and Dependent LOV in OAF.