Advertisements

Archive for August, 2013

How-To: Attach OWSM Policy to ADF BC Service

Open ServiceAMImpl and place the cursor on the Annotations section.

ServiceAMImpl

Go to ‘Web Services Extension’ in the property inspector and click on the LOV for Security that  brings up the all available OWSM policies.

policy

Select any service policy from the list based on your requirements and click on OK.

policySel

Observe that ServiceImpl is updated showing new annotation with the selected OWSM policy.

policy2

Advertisements

How-To: Populate PL/SQL Collections in ADF Code

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;
}


Advertisements

Pages

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 342 other followers

Enter your email address to follow this blog and receive notifications of new posts by email.