Home    HRIS    Bulking up your PL/SQL

Bulking up your PL/SQL

By / September 1, 2010 / / 0 Comments

You don’t know what you don’t know. It’s one of those annoyingly obvious statements people will often say when you’ve discovered something new that you had no idea existed. In the vast world of Oracle, this can often be the case.

I had this exact same situation recently during a data conversion process. I needed a way to insert all the good records into a table, and identify those that failed.

My initial approach was to place the inserts in a for loop, wrapping the insert statement in its own block. When an exception is raised, it will be recorded and control will pass to the encompassing block allowing the loop to continue:

For i in 1..pi_employee_data.last loop
  Begin
    Insert into new_employees values pi_employee_data(i);
  Exception
    When others then
      dbms_output.put_line('Insert data '||SQLERRM
                           ||' Employee#'||pi_employee_data(i).employee_id);
  End;

End loop;

commit;

Now, this is all well and good. But what if you want to stick to “best practice” and use bulk statements. I’d been making use of collections, so using a forall statement seemed a better approach. Problem being, you can’t wrap the insert statement into its own SQL block. This is where SQL%BULK_EXCEPTIONS comes in.

Note the addition of ‘save exceptions’ to the following forall statement. If any exceptions are encountered when inserting into the new_employees table, they will be saved to the SQL%BULK_EXCEPTIONS object. Only when the forall statement is finished will the exception handler be called. We’re then free to extract any data regarding the particular record that caused the exception.

function insert_data (pi_employee_data in employee_records)
  return varchar2 is

  l_no_errors number;
  dml_errors  exception;

  PRAGMA EXCEPTION_INIT(dml_errors, -24381);

begin

  forall i in 1..pi_employee_data.count save exceptions
      insert into new_employees values pi_employee_data(i);

  commit;

exception
    when dml_errors then
      l_no_errors := SQL%BULK_EXCEPTIONS.count;
      for i in 1 .. l_no_errors loop
        dbms_output.put_line('Bulk insert error: '||
           SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||
           ' Employee:'||
           pi_employee_data(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).employee_id);
      end loop;

    when others then
      dbms_output.put_line('Error:'||SQLERRM||
         ' '||dbms_utility.format_error_backtrace);

end;

As mentioned in a recent article by Tim Place, knowing the problem is one thing, knowing where to find the solution is the other. Using the Oracle documentation and having an idea which keywords to use when looking for a solution makes problems such as the above that much easier.

Conincidently, SQL%BULK_EXCEPTIONS also made an appearance in one of Steve Feuerstein’s talks at the recent InSync conference in Melbourne. As anyone who’s seen Steve before will know, collections and bulk operations should be two of the staples of any piece of PL/SQL code you write. Now with SQL%BULK_EXCEPTIONS, you’ll be able to use them much more effectively.