Bulk Processing - Inserting Rows Using Collections of Scalar Types

Example Description

  • A table is created to store the data.
  • A collection for each column type of the table is created.
  • The bulk 'FORALL' command inserts all rows at once.
  • 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
);

Collection Table of Element Type Number

Collection Table Type : BULK_TABLE_NUMBER
/**
 * Table of number
 */
create or replace type bulk_table_number force as table of number;

Collection Table of Element Type Date

Collection Table Type : BULK_TABLE_DATE
/**
 * Table of date
 */
create or replace type bulk_table_date force as table of date;

Collection Table of Element Type Varchar2

Collection Table Type : BULK_TABLE_VARCHAR
/**
 * Table of varchar2
 */
create or replace type bulk_table_varchar force as table of varchar2(100);

PL/SQL Procedure

The procedure 'BULK_COLLECTION_SCALAR' demonstrates how to work with collections.
PL/SQL Procedure : BULK_COLLECTION_SCALAR
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'.  
 */
is
begin
  -- process all rows in one command
  forall i in 1..i_col_number.count
    insert into bulk_processing_table
      (n,d,s)
      values
      (i_col_number(i), i_col_date(i), i_col_varchar(i));
end bulk_collection_scalar;

Factory API : Calling the PL/SQL package

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

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

import factory.ExamplesRPCFactory;
import plsql_workbench_examples.timer.TimerCounter;
import service.BulkCollectionScalarService;

public class BulkCollectionScalarFactoryApi {
  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
      BulkCollectionScalarService service = ExamplesRPCFactory.getBulkCollectionScalarService();

      // generating 50000 elements to transfer to the stored procedure 
      List<Integer> numberList = new ArrayList<>(ELEMENTS);
      List<Date> dateList = new ArrayList<>(ELEMENTS);
      List<String> stringList = new ArrayList<>(ELEMENTS);
      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)));
        stringList.add(UUID.randomUUID().toString());
      }

      // 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 (first call)", ELEMENTS));

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

      // print out throughput
      System.out.println(tc.perSecond("scalar bulk performance (second call)", ELEMENTS));
    }
    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 : BulkCollectionScalarSpringApi.java
package plsql_workbench_examples.springapi;

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

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.BulkCollectionScalarService;

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

  @Autowired
  private BulkCollectionScalarService bulkCollectionScalarService;

  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(BulkCollectionScalarSpringApi.class)) {
      ctx.getBean(BulkCollectionScalarSpringApi.class).runDemo();
    }
  }

  private void runDemo()
  {
    try {
      // generating 50000 elements to transfer to the stored procedure 
      List<Integer> numberList = new ArrayList<>(ELEMENTS);
      List<Date> dateList = new ArrayList<>(ELEMENTS);
      List<String> stringList = new ArrayList<>(ELEMENTS);
      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)));
        stringList.add(UUID.randomUUID().toString());
      }

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

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

      // print out throughput
      System.out.println(tc.perSecond("scalar bulk performance (first call)", ELEMENTS));

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

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