Bulk Processing - Fetching Rows Using Collections of Objects

Example Description

• A table is created to store the data (the same table as the previous example).
• An object type as collection element and the collection table type is created.
• The PL/SQL function opens a cursor to fetch a specific number of elements and returns the elements.
• A timer+counter determines the performance.

Create Table Statement

Table : BULK_PROCESSING_TABLE

create table bulk_processing_table
(
n    number not null,
d    date not null,
s    varchar2(100) not null
);

User Defined Type of Type Object

Object Type Specification : BULK_OBJECT

/**
 * Object type with three fields of number, date and varchar2.
 */
create or replace type bulk_object force as object (
  n   number,
  d   date,
  s   varchar2(100)
);

User Defined Type of Collection of Element Type Object

Collection Table Type : BULK_TABLE_OBJECT

/**
 * Table of object bulk_object
 */
create or replace type bulk_table_object force as table of bulk_object;

PL/SQL Procedure

PL/SQL Function : BULK_SELECT_COLLECTION_OBJECT

create or replace function bulk_select_collection_object(i_limit in number)
return bulk_table_object
/**
 * Demonstration of fetching table object elements.
 * 
 * @param i_limit Maximum number of elements to read.
 * @return Collection of bulk_object fetched by bulk collect.  
 */
is
  v_bulk_table_object bulk_table_object;

  -- the cursor returns elements of bulk_objects
  cursor c_bulk_processing_table is
    select bulk_object(n, d, s)
      from bulk_processing_table;
begin
  -- open the cursor
  open c_bulk_processing_table;
  
  -- fetch elements
  fetch c_bulk_processing_table
    bulk collect into v_bulk_table_object
    limit i_limit;
    
  -- close the cursor 
  close c_bulk_processing_table;
  
  return v_bulk_table_object;
end bulk_select_collection_object;

Factory API : Calling the PL/SQL package

Java Calling Class : BulkSelectCollectionObjectsFactoryApi.java

package plsql_workbench_examples.factoryapi;

import java.util.List;

import factory.ExamplesRPCFactory;
import plsql_workbench_examples.timer.TimerCounter;
import service.BulkSelectCollectionObjectService;
import transferobject.BulkObject;

public class BulkSelectCollectionObjectsFactoryApi {
  private final static int ELEMENTS = 50000;

  public static void main(String[] args)
  {
    try {
      // set database credentials and configuration parameters
      System.setProperty("dbw_examples.url", "jdbc:oracle:thin:@192.168.0.109:1521/orcl");
      System.setProperty("dbw_examples.username", "dbw_examples");
      System.setProperty("dbw_examples.password", "dbw_examples");

      // getting the service
      BulkSelectCollectionObjectService service = ExamplesRPCFactory.getBulkSelectCollectionObjectService();

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

      // calling the stored procedure
      List<BulkObject> result = service.call(ELEMENTS);

      // print out throughput
      System.out.println(tc.perSecond("objects fetch object bulk performance", result.size()));
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : BulkSelectCollectionObjectsSpringApi.java

package plsql_workbench_examples.springapi;

import java.util.List;

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.BulkSelectCollectionObjectService;
import transferobject.BulkObject;

@Component
public class BulkSelectCollectionObjectsSpringApi {
  private final static int                  ELEMENTS = 50000;

  @Autowired
  private BulkSelectCollectionObjectService bulkSelectCollectionObjectService;

  public static void main(String[] args)
  {
    // set database credentials and configuration parameters
    System.setProperty("dbw_examples.url", "jdbc:oracle:thin:@192.168.0.109:1521/orcl");
    System.setProperty("dbw_examples.username", "dbw_examples");
    System.setProperty("dbw_examples.password", "dbw_examples");

    // Register Spring Beans, Spring Context and call demo method 
    try (GenericApplicationContext ctx = BaseSpringConfig.getCtx(BulkSelectCollectionObjectsSpringApi.class)) {
      ctx.getBean(BulkSelectCollectionObjectsSpringApi.class).runDemo();
    }
  }

  private void runDemo()
  {
    try {
      // timer to check the throughput
      TimerCounter tc = new TimerCounter();

      // calling the stored procedure
      List<BulkObject> result = bulkSelectCollectionObjectService.call(ELEMENTS);

      // print out throughput
      System.out.println(tc.perSecond("objects select bulk performance", result.size()));
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}