ORA-01476: divisor is equal to zero
ORA-01476: divisor is equal to zeroA division in the SQL or PL/SQL code had 0 as the divisor.
Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14
ORA-01476 is raised when Oracle evaluates x / 0. The SQL is valid, so a report can run fine for months and then fail when one row (or one group) has 0 in the column you divide by.
The * points at the / that failed. First find the rows where the divisor is 0, then decide what the result should be for them: NULL (unknown), 0, or something else. That decision is the fix.
Causes and fixes
1A ratio column where some rows have 0 in the divisor
actual / target * 100 calculates an achievement rate. One branch, Jeju, had target = 0, and the whole query failed — the other branches were not returned either.
NULLIF(target, 0) turns 0 into NULL, and dividing by NULL gives NULL instead of an error. The query returned all 3 branches: Busan 75, Seoul 120, and an empty rate for Jeju.
Setup
CREATE TABLE sales_goal (branch VARCHAR2(20), target NUMBER, actual NUMBER);
INSERT INTO sales_goal VALUES ('Seoul', 1000, 1200);
INSERT INTO sales_goal VALUES ('Busan', 800, 600);
INSERT INTO sales_goal VALUES ('Jeju', 0, 150);
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT branch,
ROUND(actual / target * 100, 1) AS achieve_pct
FROM sales_goal
ORDER BY branch; ROUND(actual / target * 100, 1) AS achieve_pct
*
ERROR at line 2:
ORA-01476: divisor is equal to zero
Help: https://docs.oracle.com/error-help/db/ora-01476/SELECT branch, target FROM sales_goal WHERE target = 0;BRANCH TARGET
-------------------- ----------
Jeju 0
1 row selected.Find the zero rows first with WHERE target = 0. If the 0 is an input mistake, fix the data as well.
SELECT branch,
ROUND(actual / NULLIF(target, 0) * 100, 1) AS achieve_pct
FROM sales_goal
ORDER BY branch;BRANCH ACHIEVE_PCT
-------------------- -----------
Busan 75
Jeju
Seoul 120
3 rows selected.2SUM()/SUM() where a group adds up to 0
Average unit price is SUM(amount) / SUM(qty) per item. For Note, a sale of 3 and a return of -3 made SUM(qty) exactly 0. No single row has 0 — the zero only appears after grouping, which makes it easy to miss.
Check with CASE WHEN SUM(qty) = 0 THEN 0 ELSE SUM(amount) / SUM(qty) END. Note got 0 and Pen got 500.
Setup
CREATE TABLE sales_line (item VARCHAR2(20), qty NUMBER, amount NUMBER);
INSERT INTO sales_line VALUES ('Pen', 10, 5000);
INSERT INTO sales_line VALUES ('Pen', 5, 2500);
INSERT INTO sales_line VALUES ('Note', 3, 6000);
INSERT INTO sales_line VALUES ('Note', -3, -6000);
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.SELECT item,
SUM(amount) / SUM(qty) AS avg_price
FROM sales_line
GROUP BY item
ORDER BY item; SUM(amount) / SUM(qty) AS avg_price
*
ERROR at line 2:
ORA-01476: divisor is equal to zero
Help: https://docs.oracle.com/error-help/db/ora-01476/SELECT item, SUM(qty) AS total_qty, SUM(amount) AS total_amount
FROM sales_line
GROUP BY item
ORDER BY item;ITEM TOTAL_QTY TOTAL_AMOUNT
-------------------- ---------- ------------
Note 0 0
Pen 15 7500
2 rows selected.Look at the group totals (SUM(qty), SUM(amount)) before choosing between 0 and NULL. Here both totals for Note were 0.
SELECT item,
CASE WHEN SUM(qty) = 0 THEN 0
ELSE SUM(amount) / SUM(qty) END AS avg_price
FROM sales_line
GROUP BY item
ORDER BY item;ITEM AVG_PRICE
-------------------- ----------
Note 0
Pen 500
2 rows selected.3A PL/SQL function divides by a count that can be 0
avg_salary returns v_sum / v_cnt. Department 30 has no employees, so COUNT(*) was 0. The error came with ORA-06512: at "LAB_A.AVG_SALARY", line 6, the line of the RETURN.
Check the divisor before dividing: IF v_cnt = 0 THEN RETURN NULL; END IF;. After recompiling, department 10 returned 4600 and department 30 returned NULL.
Setup
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER, salary NUMBER);
INSERT INTO emp VALUES (1, 'Kim', 10, 5000);
INSERT INTO emp VALUES (2, 'Lee', 10, 4200);
COMMIT;
CREATE OR REPLACE FUNCTION avg_salary(p_dept NUMBER) RETURN NUMBER IS
v_sum NUMBER;
v_cnt NUMBER;
BEGIN
SELECT NVL(SUM(salary), 0), COUNT(*) INTO v_sum, v_cnt FROM emp WHERE dept_id = p_dept;
RETURN v_sum / v_cnt;
END;
/Table created.
1 row created.
1 row created.
Commit complete.
Function created.SELECT avg_salary(10) AS d10, avg_salary(30) AS d30 FROM dual;SELECT avg_salary(10) AS d10, avg_salary(30) AS d30 FROM dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "LAB_A.AVG_SALARY", line 6
Help: https://docs.oracle.com/error-help/db/ora-01476/CREATE OR REPLACE FUNCTION avg_salary(p_dept NUMBER) RETURN NUMBER IS
v_sum NUMBER;
v_cnt NUMBER;
BEGIN
SELECT NVL(SUM(salary), 0), COUNT(*) INTO v_sum, v_cnt FROM emp WHERE dept_id = p_dept;
IF v_cnt = 0 THEN
RETURN NULL;
END IF;
RETURN v_sum / v_cnt;
END;
/Function created.In PL/SQL, the ORA-06512 line number tells you which division failed. Start there.
SELECT avg_salary(10) AS d10, avg_salary(30) AS d30 FROM dual; D10 D30
---------- ----------
4600
1 row selected.FAQ
How do I avoid divide by zero in Oracle SQL?
Wrap the divisor in NULLIF(col, 0). In this lab actual / NULLIF(target, 0) returned NULL for the zero row and normal values for the rest.
Should the result be 0 or NULL when dividing by zero?
It depends on the meaning. NULL says "cannot be calculated"; 0 says "the value is zero". This page shows both: NULLIF for NULL, CASE for 0.
Why does ORA-01476 appear even though no column is 0?
The divisor can become 0 after calculation, for example SUM(qty) when a sale and a return cancel out. Check the group totals, not just the rows.
Which line caused ORA-01476 in my procedure?
Read the ORA-06512 line under the error. In this lab it pointed to line 6 of AVG_SALARY, the RETURN v_sum / v_cnt line.
How we verify
Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.
lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free