ORA-01476 means a division in SQL or PL/SQL had zero as its divisor. Ratios such as win rates, averages per item, or percentages fail as soon as one row has a zero count. The fix is to decide what the result should be in that case, usually NULL or zero, and say so in the expression.
Reproduce the Error
Example:
create table demo_kpi (team varchar2(10), wins number, games number);
insert into demo_kpi values ('Red', 6, 10);
insert into demo_kpi values ('Blue', 0, 0);
commit;
select team, wins / games as win_rate from demo_kpi;Output:
Table DEMO_KPI created. 1 row inserted. 1 row inserted. Commit complete. Error starting at line : 5 In command - select team, wins / games as win_rate from demo_kpi Error at Command Line : 5 Column : 19 Error report - SQL Error: ORA-01476: divisor is equal to zero
The Blue team has played no games, and one such row is enough to stop the whole query.
Fix: NULLIF or CASE
Example:
select team, wins / nullif(games, 0) as win_rate from demo_kpi; select team, case when games = 0 then 0 else wins / games end as win_rate from demo_kpi;
Output:
TEAM WIN_RATE _______ ___________ Red 0.6 Blue TEAM WIN_RATE _______ ___________ Red 0.6 Blue 0
- NULLIF(games, 0) turns a zero divisor into NULL, and dividing by NULL gives NULL: the rate is unknown.
- CASE returns 0, or any value you choose, when there is nothing to divide by.
Things to Know
- In PL/SQL, ORA-01476 is the predefined exception ZERO_DIVIDE.
- Handle zero in the expression itself wherever the divisor can be zero, rather than relying on other parts of the query to remove those rows.
- Dividing BINARY_DOUBLE values by zero returns Infinity instead of raising the error.
Related Guides
Conclusion
ORA-01476 means a divisor was zero. Guard every division whose divisor can be zero with NULLIF, to get NULL, or CASE, to get a value of your choice.
