Bulk Processing - Insert Rows Using Collections of PL/SQL Records

Example Description

• A table is created to store the data (the same table as the previous example).
• The collection of PL/SQL Record element is defined in package specification.
• 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
);

PL/SQL Package Specification

PL/SQL Package Specification : BULK_PLSQL_RECORD

create or replace package bulk_plsql_record
/**
 * Bulk collection demo package demonstration of collection of PL/SQL Records elements.
 */
as

type plsql_record is record (
  n    number,
  d    date,
  s    varchar2(100)
);
type table_plsql_record is table of plsql_record;

/**
 * Bulk collection demo with PL/SQL Record element.
 * 
 * @param i_table_plsql_record List of record elements of type 'plsql_record' to insert into table 'bulk_processing_table'.  
 */
procedure doit
(
  i_table_plsql_record   in table_plsql_record 
);

end bulk_plsql_record;

PL/SQL Package Implementation

PL/SQL Package Body : BULK_PLSQL_RECORD

create or replace package body bulk_plsql_record
as

/**
 * Bulk collection demo with PL/SQL Record element.
 * 
 * @param i_table_plsql_record List of record elements of type 'plsql_record' to insert into table 'bulk_processing_table'.  
 */
procedure doit
(
  i_table_plsql_record   in table_plsql_record 
)
is
begin
  forall i in 1..i_table_plsql_record.count
    insert into bulk_processing_table
      (n,d,s)
      values
      (i_table_plsql_record(i).n, i_table_plsql_record(i).d, i_table_plsql_record(i).s);
end doit;

end bulk_plsql_record;

Factory API : Calling the PL/SQL package

Java Calling Class : BulkCollectionPlSqlRecordFactoryApi.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.BulkPlsqlRecordService;
import transferobject.BulkPlsqlRecordTO;

public class BulkCollectionPlSqlRecordFactoryApi {
  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
      BulkPlsqlRecordService service = ExamplesRPCFactory.getBulkPlsqlRecordService();

      // generating 50000 elements to transfer to the stored procedure 
      List<BulkPlsqlRecordTO.PlsqlRecord> list = new ArrayList<>(ELEMENTS);
      for (int i = 0; i < ELEMENTS; i++) {
        BulkPlsqlRecordTO.PlsqlRecord o = new BulkPlsqlRecordTO.PlsqlRecord();
        o.d = new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE));
        o.s = UUID.randomUUID().toString();
        o.n = (int) (Math.random() * Integer.MAX_VALUE);
        list.add(o);
      }

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

      // calling the stored procedure
      service.doit(list);

      // print out throughput
      System.out.println(tc.perSecond("collection of pl/sql record element bulk performance (first call)", ELEMENTS));

      // calling the stored procedure
      service.doit(list);

      // print out throughput
      System.out.println(tc.perSecond("collection of pl/sql record element bulk performance (second call)", ELEMENTS));
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : BulkCollectionPlSqlRecordSpringApi.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.BulkPlsqlRecordService;
import transferobject.BulkPlsqlRecordTO;

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

  @Autowired
  private BulkPlsqlRecordService bulkPlsqlRecordService;

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

  private void runDemo()
  {
    try {
      // generating 50000 elements to transfer to the stored procedure 
      List<BulkPlsqlRecordTO.PlsqlRecord> list = new ArrayList<>(ELEMENTS);
      for (int i = 0; i < ELEMENTS; i++) {
        BulkPlsqlRecordTO.PlsqlRecord o = new BulkPlsqlRecordTO.PlsqlRecord();
        o.setD(new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE)));
        o.setS(UUID.randomUUID().toString());
        o.setN((int) (Math.random() * Integer.MAX_VALUE));
        list.add(o);
      }

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

      // calling the stored procedure
      bulkPlsqlRecordService.doit(list);

      // print out throughput
      System.out.println(tc.perSecond("collection of pl/sql record element bulk performance (first call)", ELEMENTS));

      // calling the stored procedure
      bulkPlsqlRecordService.doit(list);

      // print out throughput
      System.out.println(tc.perSecond("collection of pl/sql record element bulk performance (second call)", ELEMENTS));
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}