< Prev - Examples - Next >

Bulk Processing - Selecting Rows Using Collections Of Objects

Example Description

Table : BULK_PROCESSING_TABLE
create table bulk_processing_table
(
n    number not null,
d    date not null,
s    varchar2(100) not null
);
Object Type Specification : BULK_OBJECT
create or replace type bulk_object force as object (
  n   number,
  d   date,
  s   varchar2(100)
);
Collection Table Type : BULK_TABLE_OBJECT
create or replace type bulk_table_object force as table of bulk_object;
PL/SQL Function : BULK_SELECT_COLLECTION_OBJECT
create or replace function bulk_select_collection_object(i_limit in number)
return bulk_table_object
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;
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) {
      e.printStackTrace();
    }
  }
}