< Prev - Examples - Next >

Bulk Processing - Selecting Rows Using Collections Of Objects

Example Description

create table bulk_processing_table
n    number not null,
d    date not null,
s    varchar2(100) not null
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)
Collection Table Type : BULK_TABLE_OBJECT
 * Table of object bulk_object
create or replace type bulk_table_object force as table of bulk_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.  
  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;
  -- 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;
Java Calling Class : BulkSelectCollectionObjects.java
package plsql_workbench_examples;

import java.util.List;

import factory.ExamplesRPCFactory;
import service.BulkSelectCollectionObjectService;
import transferobject.BulkObject;

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

  public static void main(String[] args) {
    try {
      // 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 select bulk performance", result.size()));
    catch (Exception e) {