Siva's Blog

People tend to write lot of business logic in PL/SQL when there is lot of data to manipulate with or to improve the performance. When PL/SQL collections are the best way to accept multiple records as input and return the response.

This post shows the sample code to call a PL/SQL API accepting PL/SQL table from an ADF application module.

PL/SQL Objects:

CREATE OR REPLACE TYPE emp_rec_type AS OBJECT (
    employee_id     NUMBER(18),
    first_name      VARCHAR2(30),
    last_name       VARCHAR2(30),
    middle_name     VARCHAR2(30),
    employee_type   VARCHAR2(30),
    location        VARCHAR2(30),
    addr_line1      VARCHAR2(30),
    addr_line2      VARCHAR2(30),
    addr_line3      VARCHAR2(30)
    city            VARCHAR2(30)
);

CREATE OR REPLACE TYPE emp_tbl_type AS TABLE OF emp_record;

CREATE OR REPLACE PACKAGE emp_ddl_package AS
   PROCEDURE validate_emp_records (
      emp_rec_list        IN OUT NOCOPY emp_tbl_type,
      validation_status OUT NOCOPY VARCHAR2     
    );
END emp_ddl_package;

CREATE OR REPLACE PACKAGE BODY emp_ddl_package AS 
 
  PROCEDURE validate_emp_records(
      emp_rec_list IN OUT NOCOPY emp_tbl_type,
      validation_status OUT NOCOPY VARCHAR2
  )IS   
  BEGIN 
          — do the coding here
  END validate_emp_records;
END emp_ddl_package;

Sample Code of Java Method in Application Module:

Following is the typical piece of code that can be used in ADF application module to call the PL/SQL API:

public List<EmpVORowImpl> validateEmployees(List<EmpVORowImpl> empList) throws Exception {
  OracleCallableStatement stmt = null;
  ArrayList<EmpVORowImpl> empRespList = null;

  if (empList != null && empList.size() > 0) {
         empRespList = new ArrayList<EmpVORowImpl>();

    try {
      // Call the pl/sql API to determine invoice status
      String sql = 
"begin emp_ddl_package.validate_emp_records(emp_rec_list=>:1,validation_status=>:2); end;";

      stmt =
          (OracleCallableStatement)getDBTransaction().createCallableStatement(sql,0); 
ArrayList<STRUCT> inputEmpList  = new ArrayList<STRUCT>();

StructDescriptor structDescriptor = StructDescriptor.createDescriptor(“EMP_REC_TYPE”,stmt.getConnection());

  for (int i = 0; i < empList.size(); i++) {
        EmpVORowImpl empRow = empList.get(i);
        Object[] o =
          new Object[] { empRow.getEmployeeId(), empRow.getFirstName(),
                         empRow.getLastName(), empRow.getMiddleName(),
                         empRow.getEmployeeType(), empRow.getLocation(),
                         empRow.getAddrLine1(), empRow.getAddrLine2(),
                         empRow.getAddrLine3(), empRow.getCity() };

        STRUCT empRowStruct = new STRUCT(structDescriptor,stmt.getConnection(), o); 
        inputEmpList.add(empRowStruct);
      }

      ArrayDescriptor empArray =
        ArrayDescriptor.createDescriptor("EMP_TBL_TYPE",
                                         stmt.getConnection());
      ARRAY empArrayTbl =
        new ARRAY(empArray, stmt.getConnection(), inputEmpList.toArray());

      stmt.registerOutParameter(1, OracleTypes.ARRAY, "EMP_TBL_TYPE");
      stmt.setARRAY(1, empArrayTbl);
      stmt.registerOutParameter(2, OracleTypes.VARCHAR, 0, 1);
      stmt.execute();

      //get the output table from pl/sql API and return
      Array outputArray = stmt.getArray(1);
      String returnStatus = stmt.getString(2);

      if (returnStatus != null && "Success".equals(returnStatus) &&
          outputArray != null) {
        ResultSet rs = outputArray.getResultSet();
        EmpVORowImpl tempRow;

        while (rs != null && rs.next()) {
          Struct empRespStruct = (Struct)rs.getObject(2);
          Object[] empAttributes = empRespStruct.getAttributes();

          if (empAttributes != null) {
            tempRow = (EmpVORowImpl)getEmpVO().createRow();
          tempRow.setAttributeValues(Arrays.asList(tempRow.getAttributeNames()), Arrays.asList(empAttributes));
            empRespList.add(tempRow);
          }
        }
      }
    } catch (SQLException sqlex) {
      throw new Exception("sql exception");
    } catch (Exception ex) {
      throw new Exception("exception");
    } finally {
      try {
        stmt.close();
      } catch (SQLException sqlex) {
        throw new Exception("sql exception");
      }
    }
  }

  return empRespList;
}