Bulk Processing - Inserting Rows Using PL/SQL Tables

Example Description

• A table is created to store the data (the same table as the previous example).
• In the package specification the PL/SQL table types and the procedure is declared.
• The procedure of the package inserts all rows at once with the 'FORALL' command.
• A timer+counter determines the performance in the Java calling class.

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_TABLE

create or replace package bulk_plsql_table
/**
 * Bulk collection demo package demonstration in package defined PL/SQL tables.
 */
as

/** PL/SQL table type definitions */
type plsql_table_number is table of number index by pls_integer;
type plsql_table_date is table of varchar2(8) index by pls_integer;
type plsql_table_varchar is table of varchar2(100) index by pls_integer;

/**
 * Bulk collection demo with three input parameter of PL/SQL table.
 * Because only number and varchar2 elements are supported, the date value is converted to varchar2.
 * 
 * @param i_plsqltab_number List of number values to insert into table 'bulk_processing_table'.  
 * @param i_plsqltab_date List of date values to insert into table 'bulk_processing_table'.  
 * @param i_plsqltab_varchar List of varchar2 values to insert into table 'bulk_processing_table'.  
 */
procedure bulk_plsql_table
(
  i_plsqltab_number   in plsql_table_number, 
  i_plsqltab_date     in plsql_table_date, 
  i_plsqltab_varchar  in plsql_table_varchar
);

end bulk_plsql_table;

PL/SQL Package Body

PL/SQL Package Body : BULK_PLSQL_TABLE

create or replace package body bulk_plsql_table
as

procedure bulk_plsql_table
(
  i_plsqltab_number   in plsql_table_number, 
  i_plsqltab_date     in plsql_table_date, 
  i_plsqltab_varchar  in plsql_table_varchar
)
is
begin
  forall i in 1..i_plsqltab_number.count
    insert into bulk_processing_table
      (n,d,s)
      values
      (i_plsqltab_number(i), to_date(i_plsqltab_date(i), 'yyyy/mm/dd'), i_plsqltab_varchar(i));
end bulk_plsql_table;

end bulk_plsql_table;

Factory API : Calling the PL/SQL package

Java Calling Class : BulkCollectionPlSqlTableFactoryApi.java

package plsql_workbench_examples.factoryapi;

import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.UUID;

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

public class BulkCollectionPlSqlTableFactoryApi {
  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
      BulkPlsqlTableService service = ExamplesRPCFactory.getBulkPlsqlTableService();

      // date is transmitted as varchar2 and converted in pl/sql as date type again, because only number and varchar2 types are 
      // supported thru jdbc driver
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

      // generating 50000 elements to transfer to the stored procedure 
      Integer[] numberList = new Integer[ELEMENTS];
      String[] dateList = new String[ELEMENTS];
      String[] stringList = new String[ELEMENTS];
      for (int i = 0; i < ELEMENTS; i++) {
        numberList[i] = (int) (Math.random() * Integer.MAX_VALUE);
        dateList[i] = sdf.format(new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE)));
        stringList[i] = UUID.randomUUID().toString();
      }

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

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

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

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

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

Spring API : Calling the PL/SQL package

Java Calling Class : BulkCollectionPlSqlTableSpringApi.java

package plsql_workbench_examples.springapi;

import java.sql.Date;
import java.text.SimpleDateFormat;
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.BulkPlsqlTableService;

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

  @Autowired
  private BulkPlsqlTableService bulkPlsqlTableService;

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

  private void runDemo()
  {
    try {
      // date is transmitted as varchar2 and converted in pl/sql as date type again, because only number and varchar2 types are 
      // supported thru jdbc driver
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

      // generating 50000 elements to transfer to the stored procedure 
      Integer[] numberList = new Integer[ELEMENTS];
      String[] dateList = new String[ELEMENTS];
      String[] stringList = new String[ELEMENTS];
      for (int i = 0; i < ELEMENTS; i++) {
        numberList[i] = (int) (Math.random() * Integer.MAX_VALUE);
        dateList[i] = sdf.format(new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE)));
        stringList[i] = UUID.randomUUID().toString();
      }

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

      // calling the stored procedure
      bulkPlsqlTableService.bulkPlsqlTable(numberList, dateList, stringList);

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

      // calling the stored procedure
      bulkPlsqlTableService.bulkPlsqlTable(numberList, dateList, stringList);

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