Bulk Processing - Handle Failures / Save Exceptions

Example Description

• This examples is based on the example of 'Bulk Processing - Inserting Rows Using Collections Of Objects'.
• This example creates NULL value test data that is not allowed.
• During the insert call exception will be raised.
• These exceptions are collected and send back to the calling Java 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
);

User Defined Type of Type Object

Object Type Specification : BULK_OBJECT

/**
 * Object type with three fields of number, date and varchar2.
 */
create or replace type bulk_object force as object (
  n   number,
  d   date,
  s   varchar2(100)
);

User Defined Type of Collection of Element Type Object

Collection Table Type : BULK_TABLE_OBJECT

/**
 * Table of object bulk_object
 */
create or replace type bulk_table_object force as table of bulk_object;

User Defined Type of Collection of Element Type Object

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

PL/SQL Function : BULK_SAVE_EXCEPTIONS

create or replace function bulk_save_exceptions(i_col_object in bulk_table_object)
return bulk_table_varchar
/**
 * Demo how to process exceptions during bulk processing.
 * 
 * @param i_col_object List of object type values to insert into table 'bulk_processing_table'.
 * @return Collection of varchar2 containing exception information.  
 */
is
  result   bulk_table_varchar;
  ex_idx   number;
  ex_code  number;
begin
  result := bulk_table_varchar();
  
  begin
    forall i in 1..i_col_object.count save exceptions
      insert into bulk_processing_table
        (n,d,s)
        values
        (i_col_object(i).n, i_col_object(i).d, i_col_object(i).s);
  exception when others then
    for k in 1..sql%bulk_exceptions.count loop
      ex_idx := sql%bulk_exceptions(k).error_index;
      ex_code := sql%bulk_exceptions(k).error_code;
      result.extend();
      result(result.count) := 'idx:' || ex_idx || ' - msg:' || sqlerrm(-ex_code);
    end loop;
  end;
  
  return result;
end bulk_save_exceptions;

Factory API : Calling the PL/SQL package

Java Calling Class : BulkSaveExceptionsFactoryApi.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 service.BulkSaveExceptionsService;
import transferobject.BulkObject;

public class BulkSaveExceptionsFactoryApi {
  private final static int ELEMENTS = 200;

  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
      BulkSaveExceptionsService service = ExamplesRPCFactory.getBulkSaveExceptionsService();

      // generating 500 elements to transfer to the stored procedure 
      List<BulkObject> objectList = new ArrayList<>();
      for (int i = 0; i < ELEMENTS; i++) {
        BulkObject o = new BulkObject();
        o.d = new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE));

        if (i % 10 == 0) {
          // produce failure : null value not allowed for column
          o.s = null;
        }
        else {
          o.s = UUID.randomUUID().toString();
        }
        o.n = (int) (Math.random() * Integer.MAX_VALUE);
        objectList.add(o);
      }

      // calling the stored procedure
      List<String> results = service.call(objectList);

      // print out the error messages
      for (String errorMsg : results) {
        System.out.println(errorMsg);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Spring API : Calling the PL/SQL package

Java Calling Class : BulkSaveExceptionsSpringApi.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 service.BulkSaveExceptionsService;
import transferobject.BulkObject;

@Component
public class BulkSaveExceptionsSpringApi {
  private final static int          ELEMENTS = 200;

  @Autowired
  private BulkSaveExceptionsService bulkSaveExceptionsService;

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

  private void runDemo()
  {
    try {
      // getting the service
      // generating 500 elements to transfer to the stored procedure 
      List<BulkObject> objectList = new ArrayList<>();
      for (int i = 0; i < ELEMENTS; i++) {
        BulkObject o = new BulkObject();
        o.setD(new Date(System.currentTimeMillis() + (long) (Math.random() * Integer.MAX_VALUE)));

        if (i % 10 == 0) {
          // produce failure : null value not allowed for column
          o.setS(null);
        }
        else {
          o.setS(UUID.randomUUID().toString());
        }
        o.setN((int) (Math.random() * Integer.MAX_VALUE));
        objectList.add(o);
      }

      // calling the stored procedure
      List<String> results = bulkSaveExceptionsService.call(objectList);

      // print out the error messages
      for (String errorMsg : results) {
        System.out.println(errorMsg);
      }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}