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)

How to handle errors in Oracle PL/SQL

How to handle errors in Oracle PL/SQL

How to handle errors in Oracle PL/SQL
How to handle errors in Oracle PL/SQL (2)

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 and instead just use pure SQL but let's carry on anyway!

When using an Oracle PL/SQL procedure to insert records into the database one at a time each insert statement is considered to be a separate transaction. This means that none of the insert statements after the insert that failed will be executed (unless the error was trapped by an exception handler and you have the necessary logic to re-start the inserts after an error). In other words if the 4th insert out of 10 inserts fails, then the first 3 records will be in the database but none 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 first thing our little block of code does is to count the number of records in the table (and write this out using dbms_output). Then we have 4 insert statements followed by a commit (to ensure that Oracle saves all the changes) followed by th exception handler.

In this piece of PL/SQL, the 3rd of the 4 insert statements fails because the job_id column has a not null constraint without a default value and we haven't supplied a value for it in the insert statement. Note that the code is syntactically correct but fails at runtime. When this insert statement fails Oracle, raises an exception and normal execution of the block stops and in this case jumps to the execution handler. If there were no exception handler then control would be returned to the controlling environment. In this case the exception handler counts the number of records in the table again.

The output from this procedure is:

records BEFORE = 0 records AFTER = 2

i.e. the first 2 records were inserted.

If we were to change the procedure to insert records in a loop, the same thing would happen - any records inserted before an exception occured would not be rolled back by Oracle unless we issued an explicit rollback in the exception handler or the exception remained unhandled when control 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 records inserted by that block are not rolled back) but the first record inserted will not be rolled back by Oracle 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 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 whereas with the 2nd option, all inserts are rolled back so you can start from the beginning every time. However if you are inserting a large number of records into the table in your Oracle database then you might prefer to not start from the beginning every time.

Another alternative, if you have the data in a PL/SQL collection is to use the FORALL statement. This also has the advantage of improving performance by minimising context switches by Oracle from PL/SQL to SQL and back again,

For more help with PL/SQL see our PL/SQL tutorials and the other PL/SQL questions on our site as well as the Oracle Database PL/SQL Language Reference.

If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.