< Prev - Examples - Next >

Bulk Processing - Handle Failures / Save Exceptions

Example Description

Table : BULK_PROCESSING_TABLE
create table bulk_processing_table
(
n    number not null,
d    date not null,
s    varchar2(100) not null
);
Object Type Specification : BULK_OBJECT
create or replace type bulk_object force as object (
  n   number,
  d   date,
  s   varchar2(100)
);
Collection Table Type : BULK_TABLE_OBJECT
create or replace type bulk_table_object force as table of bulk_object;
Collection Table Type : BULK_TABLE_VARCHAR
create or replace type bulk_table_varchar force as table of varchar2(100);
PL/SQL Function : BULK_SAVE_EXCEPTIONS
create or replace function bulk_save_exceptions(i_col_object in bulk_table_object)
return bulk_table_varchar
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;
Java Calling Class : BulkSaveExceptions.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.BulkSaveExceptionsService;
import transferobject.BulkObject;

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

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

      // generating 500 elements to transfer to the stored procedure 
      List<BulkObject> objectList = new ArrayList<BulkObject>();
      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();
    }
  }
}