While inserting 10 rows into an Oracle database using a procedure, if the 3rd entry has an error, what will be the output? How can we handle that error? How can we restart from the 4th record?

by jamuna
(hyderabad)

While inserting 10 rows into an Oracle database using a procedure, if the 3rd entry has an error, what will be the output? How can we handle that error? How can we restart from the 4th record?

If you're only inserting 10 records it is probably easier to not use a procedure to do so but let's carry on anyway.

When using an Oracle PL/SQL procedure to insert records into the database, each insert statement is considered to be a separate transaction. This means that all insert statements after the insert that failed will not be executed. In other words if the 4th out of 10 inserts fails then the first 3 records will be in the database but not any of the others. We can prove this with the following block of PL/SQL code.

declare

  init_count number;
  final_count number;

begin

  select count(*) into init_count
  from employees where employee_id<0;

  insert into employees
  (employee_id,email,last_name,hire_date,job_id)
  values
  (-2,'yes2@yes.com','lll',sysdate,'IT_PROG');

  insert into employees
  (employee_id,email,last_name,hire_date,job_id)
  values
  (-3,'yes3@yes.com','lll',sysdate,'IT_PROG');

  insert into employees
  (employee_id,email,last_name,hire_date)
  values
  (-4,'yes4@yes.com','lll',SYSDATE);

  insert into employees
  (employee_id,email,last_name,hire_date,job_id)
  values
  (-5,'yes3@yes.com','lll',sysdate,'IT_PROG');

  commit;

exception when others then

  select count(*) into final_count
  from employees where employee_id<0;

  dbms_output.put_line('records BEFORE = '||init_count||
    ' records AFTER = '||final_count);

end;


This uses the employees table in the sample HR schema provided with the Oracle database. The 3rd of the 4 insert statements fails because job_id is a not null column (without a default value) and we haven't supplied a value for it. When this statement fails Oracle raises an exception and normal execution of the block stops an in this case jumps to the execution handler. The output from this procedure is:

records BEFORE = 0 records AFTER = 2

i.e. the first 2 records were inserted.

If we change the procedure to insert records in a loop, the same thing happens - those records inserted before an exception occurs are not rolled back by Oracle unless we explicitly rollback in an exception handler or the exception remains unhandled when control is returned to the executing environment. This following block of code for example:


declare

  init_count number;
  final_count number;

begin

  select count(*) into init_count
  from employees where employee_id<0;

  for i in 1..10 loop

     insert into employees
     (employee_id,email,last_name,hire_date,job_id)
     values
     (-i,'yes2@yes.com','lll',sysdate,'IT_PROG');

  end loop;

exception when others then

  select count(*) into final_count
  from employees where employee_id<0;

  dbms_output.put_line('records BEFORE = '||init_count||
    ' records AFTER = '||final_count);

end;


will fail on the 2nd insert (assuming that it is run after the first block and the inserts were not rolled back) but the first record inserted will not be rolled back unless we explicitly do so or the exception is not handled.

In answer to your questions then, if the 4th of 10 inserts fails, then you can either trap the error with an Oracle PL/SQL exception handler. In this case the first 3 records will be in the database. Or you can let the exception go unhandled all the way back to the controlling environment (SQL*Plus for example) in which case all the inserts will be rolled back automatically by Oracle. There is an advantage to this 2nd option namely that the code will be re-runnable as is. If you take the 1st option you will have to modify your code to remove the inserts that have already succeeded with the 2nd option, all inserts are rolled back so you can start from the beginning every time.

For more in-depth help, enquire about our instructor-led Oracle training courses in the UK and the US and our classroom quality online training.