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.
