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

3 Responses to “How-To: Populate PL/SQL Collections in ADF Code”


  1. 1 Anonymous April 23, 2015 at 8:14 PM

    Very nice. Thanks for the details.
    The code below is written in the managed bean on a button ‘Process’. The “ToMove” is a check box (transient) attribute on the ADF table. I would like to pass only few of the attributes from the selected rows to a plsql stored procedure as a plsql table of records. Exactly opposite to what you have explained in this blog. Can you pl suggest a best way to do this?
    package oracle.summit.selfservice.view;

    import javax.faces.event.ActionEvent;
    import oracle.adf.model.BindingContext;
    import oracle.adf.model.binding.DCBindingContainer;
    import oracle.adf.model.binding.DCIteratorBinding;
    import oracle.jbo.Row;
    import oracle.jbo.ViewObject;

    public class ProcessSelectedBean {
    public ProcessSelectedBean() {
    }

    public DCBindingContainer getDCBindingsContainer () {
    DCBindingContainer bindingsContainer =
    (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();

    return bindingsContainer;
    }

    public void ProcessSelectedMtd(ActionEvent actionEvent) {
    // Add event code here…
    DCBindingContainer bindings = this.getDCBindingsContainer();
    DCIteratorBinding iterBinding = bindings.findIteratorBinding(“EqptMovementHist2Iterator”);

    ViewObject vo = iterBinding.getViewObject();
    Row[] selectedAssetRows = vo.getFilteredRows(“ToMove”, true);
    System.out.println(“No of assets selected: “+selectedAssetRows.length);
    System.out.println(“MoveGrpID ## AssetNum ## AssetGroup”);

    for (Row row : selectedAssetRows) {
    System.out.println(row.getAttribute(“MoveGroupId”)+” ## “+row.getAttribute(“AssetSerialNumber”)+” ## “+row.getAttribute(“AssetGroup”));
    }

    // Code snippet to refresh the ADF table
    iterBinding.executeQuery();

    }
    }

    Look forward to your help.
    Thanks

    • 2 svgonugu April 23, 2015 at 8:29 PM

      Is not possible to create pl/sql table as explained in this blog with the columns that you require? And i hope that you are planning to write the code in AM.

      • 3 Anonymous April 23, 2015 at 10:29 PM

        Thanks Shiva. So how else can I pass the entire selected row sets as PLSQL table to a call to stored procedure? The stored procedure can be modified to accept all the attribute values of the rowset in a PLSQL table. I am novice at Java but expert at PLSQL.
        Thank you for your time.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Advertisements

Pages

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

Join 341 other followers

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