< Prev - Examples - Next >

Bulk Processing - Fetching Rows Using Collections of Rowtypes

Example Description

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

PL/SQL Package Specification

PL/SQL Package Specification : BULK_SELECT_COLLECTION_ROWTYPE
create or replace package bulk_select_collection_rowtype
/**
 * Bulk collection demo package demonstration of collection of Rowtype elements.
 */
as

type table_rowtype is table of bulk_processing_table%rowtype;

/**
 * Bulk collection demo with Rowtype element.
 * 
 * @param i_limit Number of rows to fetch.  
 * @param o_table_rowtype List of record elements of type 'BULK_PROCESSING_TABLE%ROWTYPE' fetched from table 'BULK_PROCESSING_TABLE'.  
 */
procedure doit
(
  i_limit           in  number,
  o_table_rowtype   out table_rowtype 
);

end bulk_select_collection_rowtype;

PL/SQL Package Implementation

PL/SQL Package Body : BULK_SELECT_COLLECTION_ROWTYPE
create or replace package body bulk_select_collection_rowtype
as

/**
 * Bulk collection demo with Rowtype element.
 * 
 * @param i_limit Number of rows to fetch.  
 * @param o_table_rowtype List of record elements of type 'BULK_PROCESSING_TABLE%ROWTYPE' fetched from table 'BULK_PROCESSING_TABLE'.  
 */
procedure doit
(
  i_limit           in  number,
  o_table_rowtype   out table_rowtype 
)
is
begin
  -- fetch elements (better solution:fetch cursor ... bulk collect ... limit ...)
  select * 
    bulk collect
    into o_table_rowtype
    from bulk_processing_table
    where rownum <= i_limit;
end doit;

end bulk_select_collection_rowtype;

Factory API : Calling the PL/SQL package

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

import java.util.List;

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

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

  public static void main(String[] args) {
    try {
      // getting the service
      BulkSelectCollectionRowtypeService service = ExamplesRPCFactory.getBulkSelectCollectionRowtypeService();

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

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

      // print out throughput
      System.out.println(tc.perSecond("objects fetch rowtype bulk performance", result.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 : BulkSelectCollectionRowtypeSpringApi.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.BulkSelectCollectionRowtypeService;
import transferobject.BulkProcessingTable;

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

  @Autowired
  private BulkSelectCollectionRowtypeService bulkSelectCollectionRowtypeService;

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

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

      // calling the stored procedure
      List<BulkProcessingTable> result = bulkSelectCollectionRowtypeService.doit(ELEMENTS);

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

< Prev - Examples - Next >