How to Handle ORA-01476: Divisor Is Equal to Zero in PL/SQL Loops

ORA-01476 in PL/SQL is the predefined exception ZERO_DIVIDE, raised when a division has a zero divisor. In a loop that processes many rows, one zero is enough to stop the whole block, and the block's earlier changes are undone with it. This guide shows how to keep a batch running: handle ZERO_DIVIDE for each row, or avoid the division by zero in the SQL itself.

Reproduce the Error

The route DEL-GOI has no seats sold:

Example:

create table demo_route_stats (route_code varchar2(10), revenue number,
                               seats_sold number, per_seat number);
insert into demo_route_stats values ('DEL-BOM', 24000, 200, null);
insert into demo_route_stats values ('DEL-GOI', 0, 0, null);
insert into demo_route_stats values ('DEL-DXB', 62000, 310, null);
commit;
set serveroutput on
begin
  for r in (select route_code, revenue, seats_sold
            from   demo_route_stats order by route_code) loop
    update demo_route_stats set per_seat = r.revenue / r.seats_sold
    where  route_code = r.route_code;
    dbms_output.put_line(r.route_code || ' done');
  end loop;
end;
/
-- the updates made before the error are gone too
select route_code, per_seat from demo_route_stats order by route_code;

Output:

Table DEMO_ROUTE_STATS created.

1 row inserted.

1 row inserted.

1 row inserted.

Commit complete.

DEL-BOM done
DEL-DXB done

begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 4
ORA-06512: at line 4

ROUTE_CODE       PER_SEAT
_____________ ___________
DEL-BOM
DEL-DXB
DEL-GOI

Two routes print done before DEL-GOI fails, yet PER_SEAT is empty for all three: the unhandled error rolled back the updates the block had already made.

Fix: Handle ZERO_DIVIDE for Each Row

An inner BEGIN ... EXCEPTION ... END around the work of one row catches the error for that row only:

Example:

set serveroutput on
-- handle ZERO_DIVIDE for each row, so one bad row does not stop the rest
begin
  for r in (select route_code, revenue, seats_sold
            from   demo_route_stats order by route_code) loop
    begin
      update demo_route_stats set per_seat = r.revenue / r.seats_sold
      where  route_code = r.route_code;
      dbms_output.put_line(r.route_code || ' done');
    exception
      when zero_divide then
        dbms_output.put_line(r.route_code || ' skipped: no seats sold');
    end;
  end loop;
end;
/
select route_code, per_seat from demo_route_stats order by route_code;
rollback;

Output:

DEL-BOM done
DEL-DXB done
DEL-GOI skipped: no seats sold

PL/SQL procedure successfully completed.

ROUTE_CODE       PER_SEAT
_____________ ___________
DEL-BOM               120
DEL-DXB               200
DEL-GOI

Rollback complete.

Fix: Avoid the Division in SQL

When zero should simply give no value, NULLIF in a single UPDATE is shorter and faster than a loop:

Example:

-- or avoid the division by zero in the statement
update demo_route_stats set per_seat = revenue / nullif(seats_sold, 0);
select route_code, per_seat from demo_route_stats order by route_code;
rollback;

Output:

3 rows updated.

ROUTE_CODE       PER_SEAT
_____________ ___________
DEL-BOM               120
DEL-DXB               200
DEL-GOI

Rollback complete.

Things to Know

• Handle only ZERO_DIVIDE in the inner block; WHEN OTHERS there would also hide errors you need to see.
• Log skipped rows to a table, not only with DBMS_OUTPUT, when the batch runs unattended.
• For bulk DML with FORALL, SAVE EXCEPTIONS collects the errors of individual rows in the same spirit.

Related Guides

• How to Fix ORA-01476: Divisor Is Equal to Zero in Oracle
• Oracle PL/SQL Bulk Collect With Save Exceptions Example

Conclusion

In a PL/SQL loop, ORA-01476 stops the batch and undoes its work. Handle ZERO_DIVIDE inside the loop to skip and report just the bad row, or remove the zero divisor with NULLIF or CASE in the statement.