create or replace package P_bulk_bind is
  procedure Bulk_Insert_Pre_10g ( i_make_sparse in boolean );
  procedure Bulk_Insert_With_Indices_Of ( i_cause_exception in boolean );
  procedure Bulk_Insert_With_Values_Of (
    i_null_email in boolean,
    i_violate_pk in boolean );
end P_bulk_bind;
/
Show Errors
--------------------------------------------------------------------------------
create or replace package body P_bulk_bind is

-- Not new features until "New features start here".

bulk_errors exception;
pragma exception_init ( bulk_errors, -24381 );

type Emp_Recs_T    is table of employees%rowtype;
g_emp_recs         Emp_Recs_T;

type Values_Of_Tab_t  is table of pls_integer index by pls_integer;
g_values_of_tab       Values_Of_Tab_t; -----------------------------------------

procedure Truncate_Destination is
begin
  execute immediate 'truncate table new_employees';
end Truncate_Destination; ------------------------------------------------------

procedure Load_Arry is
begin
  select *
    bulk collect into g_emp_recs
    from employees
    order by last_name;
end Load_Arry; -----------------------------------------------------------------

procedure Make_Sparse is
  -- THIS SIMULATES COMPLEX PROCESSING - TOO ELABORATE FOR PURE SQL -
  -- THAT RESULTS IN A SPARSE ARRAY FOR BULK INSERT
  v_prev_first_char  char(1) := Substr ( g_emp_recs(1).last_name, 1, 1 );
  v_curr_first_char  char(1);
begin
  -- Delete all elements except the first for
  -- each initial letter of last_name
  if g_emp_recs.Count() > 1
  then
    for j in 2..g_emp_recs.Last()
    loop
      v_curr_first_char := Substr ( g_emp_recs(j).last_name, 1, 1 );
      case v_curr_first_char = v_prev_first_char
        when true then
          g_emp_recs.Delete(j);
        when false then
          v_prev_first_char := v_curr_first_char;
      end case;
    end loop;
  end if;
end Make_Sparse; ---------------------------------------------------------------

procedure Cause_Exception_For_Indexes_Of is
  k pls_integer := g_emp_recs.First();
begin
  while k is not null
  loop
    -- Generate a condition to cause an exception on insert
    if g_emp_recs(k).last_name in ( 'Ernst', 'Urman' )
      then g_emp_recs(k).email := null; end if;
    k := g_emp_recs.Next(k);
  end loop;
end Cause_Exception_For_Indexes_Of; --------------------------------------------

procedure Show_Sparse_Array is
  k pls_integer := g_emp_recs.First();
begin
  while k is not null
  loop
    Dbms_Output.Put_Line ( Lpad(k,3) || ' ' || g_emp_recs(k).last_name );
    k := g_emp_recs.Next(k);
  end loop;
end Show_Sparse_Array; ---------------------------------------------------------

procedure Point_To_Sparse is
  -- THIS SIMULATES COMPLEX PROCESSING - TOO ELABORATE FOR PURE SQL -
  -- THAT RESULTS IN POINTING TO SOME ELEMENTS OF AN ARRAY FOR BULK INSERT
  v_prev_first_char  char(1) := Substr ( g_emp_recs(1).last_name, 1, 1 );
  v_curr_first_char  char(1);
begin
  -- Point to the first element for
  -- each initial letter of last_name
  g_values_of_tab(1) := 1;
  if g_emp_recs.Count() > 1
  then
    for j in 2..g_emp_recs.Last()
    loop
      v_curr_first_char := Substr ( g_emp_recs(j).last_name, 1, 1 );
      case v_curr_first_char = v_prev_first_char
        when true then
          null;
        when false then
          g_values_of_tab(g_values_of_tab.Last()+1) := j;
          v_prev_first_char := v_curr_first_char;
      end case;
    end loop;
  end if;
end Point_To_Sparse; -----------------------------------------------------------

procedure Cause_Exception_For_Values_Of (
  i_null_email in boolean,
  i_violate_pk in boolean )
is
  v_ernst               pls_integer;
  v_urman               pls_integer;
begin
  -- Find "Ernst" and "Urman"
  for j in 1..g_emp_recs.Last()
  loop
    if g_emp_recs(j).last_name = 'Ernst'
    then
      if i_null_email
        then g_emp_recs(j).email := null; end if;
      v_ernst := j;
    end if;
    if g_emp_recs(j).last_name = 'Urman'
    then
      if i_null_email
        then g_emp_recs(j).email := null; end if;
      v_urman := j;
    end if;
  end loop;

  if i_violate_pk
  then
    g_values_of_tab(g_values_of_tab.Last()+1) := v_ernst;
    g_values_of_tab(g_values_of_tab.Last()+1) := v_urman;
  end if;
end Cause_Exception_For_Values_Of; ---------------------------------------------

procedure Show_Pointed_To_Elements is
  k pls_integer;
begin
  for j in 1..g_values_of_tab.Last()
  loop
    k := g_values_of_tab(j);
    Dbms_Output.Put_Line (
     Lpad(k,3) || ' ' || g_emp_recs(k).last_name );
  end loop;
end Show_Pointed_To_Elements; --------------------------------------------------

procedure Bulk_Insert_Pre_10g ( i_make_sparse in boolean ) is
begin
  Truncate_Destination();
  Load_Arry();
  if i_make_sparse
    then Make_Sparse(); end if;
  Show_Sparse_Array();

  forall j in g_emp_recs.First()..g_emp_recs.Last()
    save exceptions
    insert into new_employees values g_emp_recs(j);

exception when bulk_errors then
  for j in 1..sql%bulk_exceptions.Count()
  loop
    Dbms_Output.Put_Line ( 'Error from element #' ||
      To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
      Sqlerrm(-sql%bulk_exceptions(j).error_code) );
  end loop;
end Bulk_Insert_Pre_10g; -------------------------------------------------------

-- New features start here.

procedure Bulk_Insert_With_Indices_Of ( i_cause_exception in boolean ) is
begin
  Truncate_Destination(); -- Set up. ( No new features yet. ) --------
  Load_Arry();                                                      --
  Make_Sparse();                                                    --
  if i_cause_exception                                              --
    then Cause_Exception_For_Indexes_Of; end if;                    --
  Show_Sparse_Array(); -----------------------------------------------

  forall j in indices of g_emp_recs
    -- between g_emp_recs.First() and g_emp_recs.Last() -- optional
    save exceptions
    insert into new_employees values g_emp_recs(j);

exception when bulk_errors then
  declare
    v_iteration  pls_integer;
    n            pls_integer;
    k            pls_integer;
  begin
    for j in 1..sql%bulk_exceptions.Count()
    loop
      v_iteration := sql%bulk_exceptions(j).error_index;
      Dbms_Output.Put_Line (
        'Error on the ' || To_Char(v_iteration) || 'th iteration' );

      -- Find the index of the offending element from the iteration number
      n := 0;
      k := g_emp_recs.First();
      while k is not null
      loop
        n := n + 1;
        if n = v_iteration
        then
        Dbms_Output.Put_Line (
          'last_name for error element: ' || g_emp_recs(k).last_name );
          exit;
        end if;
        k := g_emp_recs.Next(k);
      end loop;

      Dbms_Output.Put_Line (
        'Error was: ' || Sqlerrm(-sql%bulk_exceptions(j).error_code) );
    end loop;
  end;
end Bulk_Insert_With_Indices_Of; -----------------------------------------------

procedure Bulk_Insert_With_Values_Of (
  i_null_email in boolean,
  i_violate_pk in boolean )
is
begin
  Truncate_Destination(); -- Set up. ( No new features yet. ) --------
  Load_Arry();                                                      --
  Point_To_Sparse();                                                --
  if i_null_email or i_violate_pk                                   --
  then                                                              --
    Cause_Exception_For_Values_Of ( i_null_email, i_violate_pk );   --
  end if;                                                           --
  Show_Pointed_To_Elements(); ----------------------------------------
  
  forall j in values of g_values_of_tab
    save exceptions
    insert into new_employees values g_emp_recs(j);
  
exception when bulk_errors then
  declare
    v_iteration  pls_integer;
  begin
    for j in 1..sql%bulk_exceptions.Count()
    loop
      v_iteration := sql%bulk_exceptions(j).error_index;
      Dbms_Output.Put_Line (
        'Error on the ' || To_Char(v_iteration) || 'th iteration' );

      -- Find the index of the offending element from the iteration number
      Dbms_Output.Put_Line (
        'last_name for error element: ' ||
        g_emp_recs(g_values_of_tab(v_iteration)).last_name );

      Dbms_Output.Put_Line (
        'Error was: ' || Sqlerrm(-sql%bulk_exceptions(j).error_code) );
    end loop;
  end;
  rollback;
end Bulk_Insert_With_Values_Of; ------------------------------------------------

end P_bulk_bind;
/
Show Errors