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.