< Prev - Examples - Next >

Collections of Rowtypes

Example Description

PL/SQL Package Specification

PL/SQL Package Specification : COLLECTIONS_OF_ROWTYPE
create or replace package collection_of_rowtype
as
/**
 * Demonstration of using collections of rowtype elements in combination of bulk processing.
 */

type table_rowtype is table of bulk_processing_table%rowtype;

/**
 * Demonstration of calling a stored procedure with input of table of 'BULK_PROCESSING_TABLE%ROWTYPE' elements. 
 * The elements are written into table 'BULK_PROCESSING_TABLE' and read from the table 'BULK_PROCESSING_TABLE'.
 *
 * @param i_table_rowtype Input list of 'bulk_processing_table%rowtype' elements.
 * @param o_table_rowtype Output list of 'bulk_processing_table%rowtype' elements.
 */
procedure doit
(
  i_table_rowtype  in    table_rowtype,
  o_table_rowtype  out   table_rowtype
);
  
end collection_of_rowtype;

PL/SQL Package Implementation

PL/SQL Package Body : COLLECTIONS_OF_ROWTYPE
create or replace package body collection_of_rowtype
as

/**
 * Demonstration of calling a stored procedure with input of table of 'BULK_PROCESSING_TABLE%ROWTYPE' elements. 
 * The input elements are written into table 'BULK_PROCESSING_TABLE' and the read result from the table 'BULK_PROCESSING_TABLE' is returned.
 *
 * @param i_table_rowtype Input list of 'bulk_processing_table%rowtype' elements.
 * @param o_table_rowtype Output list of 'bulk_processing_table%rowtype' elements.
 */
procedure doit
(
  i_table_rowtype  in    table_rowtype,
  o_table_rowtype  out   table_rowtype
)
is
begin
  -- write elements 
  forall i in 1..i_table_rowtype.count
    insert into bulk_processing_table
      values i_table_rowtype(i);

  -- fetch elements (better solution:fetch cursor ... bulk collect ... limit ...)
  select * 
    bulk collect
    into o_table_rowtype
    from bulk_processing_table
    where rownum <= 10000;
end doit;  


end collection_of_rowtype;

Factory API : Calling the PL/SQL package

Using the static factory to get the remote service.
Java Calling Class : CollectionsOfRowtypeFactoryApi.java
package plsql_workbench_examples.factoryapi;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import factory.ExamplesRPCFactory;
import plsql_workbench_examples.timer.TimerCounter;
import service.CollectionOfRowtypeService;
import transferobject.BulkProcessingTable;

public class CollectionsOfRowtypeFactoryApi {
  private final static int ELEMENTS = 10000;

  public static void main(String[] args) {
    try {
      // get the service
      CollectionOfRowtypeService service = ExamplesRPCFactory.getCollectionOfRowtypeService();

      // generating 10000 elements to transfer to the stored procedure 
      List<BulkProcessingTable> list = new ArrayList<>();
      for (int i = 0; i < ELEMENTS; i++) {
        BulkProcessingTable o = new BulkProcessingTable();
        o.d = new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE));
        o.s = UUID.randomUUID().toString();
        o.n = (int) (Math.random() * Integer.MAX_VALUE);
        list.add(o);
      }

      // timer to check the throughput
      TimerCounter tc = new TimerCounter();

      // calling the stored procedure
      List<BulkProcessingTable> resultList = service.doit(list);

      // print out throughput
      System.out.println(tc.perSecond("rowtype bulk performance write+read", list.size() + resultList.size()));
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Using Spring annotation to inject the service and call the remote service.
Java Calling Class : CollectionsOfRowtypeSpringApi.java
package plsql_workbench_examples.springapi;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.GenericApplicationContext;
import org.springframework.stereotype.Component;

import plsql_workbench_examples.timer.TimerCounter;
import service.CollectionOfRowtypeService;
import transferobject.BulkProcessingTable;

@Component
public class CollectionsOfRowtypeSpringApi {
  private final static int           ELEMENTS = 10000;

  @Autowired
  private CollectionOfRowtypeService collectionOfRowtypeService;

  public static void main(String[] args) {
    // Register Spring Beans, Spring Context and call demo method 
    try (GenericApplicationContext ctx = BaseSpringConfig.getCtx(CollectionsOfRowtypeSpringApi.class)) {
      ctx.getBean(CollectionsOfRowtypeSpringApi.class).runDemo();
    }
  }

  public void runDemo() {
    try {
      // generating 10000 elements to transfer to the stored procedure 
      List<BulkProcessingTable> list = new ArrayList<>();
      for (int i = 0; i < ELEMENTS; i++) {
        BulkProcessingTable o = new BulkProcessingTable();
        o.setD(new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE)));
        o.setS(UUID.randomUUID().toString());
        o.setN((int) (Math.random() * Integer.MAX_VALUE));
        list.add(o);
      }

      // timer to check the throughput
      TimerCounter tc = new TimerCounter();

      // calling the stored procedure
      List<BulkProcessingTable> resultList = collectionOfRowtypeService.doit(list);

      // print out throughput
      System.out.println(tc.perSecond("rowtype bulk performance write+read", list.size() + resultList.size()));
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

< Prev - Examples - Next >