< Prev - Examples - Next >

Bulk Processing - Inserting Rows Using Collections Of Scalar Types

Example Description

create table bulk_processing_table
n    number not null,
d    date not null,
s    varchar2(100) not null
Collection Table Type : BULK_TABLE_NUMBER
 * Table of number
create or replace type bulk_table_number force as table of number;
Collection Table Type : BULK_TABLE_DATE
 * Table of date
create or replace type bulk_table_date force as table of date;
Collection Table Type : BULK_TABLE_VARCHAR
 * Table of varchar2
create or replace type bulk_table_varchar force as table of varchar2(100);
create or replace procedure bulk_collection_scalar
  i_col_number  in bulk_table_number, 
  i_col_date    in bulk_table_date, 
  i_col_varchar in bulk_table_varchar
 * Bulk collection demo with three input parameter of table with scalar element.
 * @param i_col_number List of number values to insert into table 'bulk_processing_table'.  
 * @param i_col_date List of date values to insert into table 'bulk_processing_table'.  
 * @param i_col_varchar List of varchar2 values to insert into table 'bulk_processing_table'.  
  -- process all rows in one command
  forall i in 1..i_col_number.count
    insert into bulk_processing_table
      (i_col_number(i), i_col_date(i), i_col_varchar(i));
end bulk_collection_scalar;
Java Calling Class : BulkCollectionScalar.java
package plsql_workbench_examples;

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

import factory.ExamplesRPCFactory;
import service.BulkCollectionScalarService;

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

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

      // generating 50000 elements to transfer to the stored procedure 
      List<Integer> numberList = new ArrayList<Integer>();
      List<Date> dateList = new ArrayList<Date>();
      List<String> stringList = new ArrayList<String>();
      for (int i = 0; i < ELEMENTS; i++) {
        numberList.add((int) (Math.random() * Integer.MAX_VALUE));
        dateList.add(new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE)));

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

      // calling the stored procedure
      service.call(numberList, dateList, stringList);

      // print out throughput
      System.out.println(tc.perSecond("scalar bulk performance", ELEMENTS));
    catch (Exception e) {