ORA-01476: divisor is equal to zero

ORA-01476: divisor is equal to zero

A 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

  1. A ratio column where some rows have 0 in the divisor
  2. SUM()/SUM() where a group adds up to 0
  3. A PL/SQL function divides by a count that can be 0

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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT branch,
       ROUND(actual / target * 100, 1) AS achieve_pct
  FROM sales_goal
 ORDER BY branch;
OUTPUT
       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/
Checkrun as lab_a
SELECT branch, target FROM sales_goal WHERE target = 0;
OUTPUT
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.

After the fixrun as lab_a
SELECT branch,
       ROUND(actual / NULLIF(target, 0) * 100, 1) AS achieve_pct
  FROM sales_goal
 ORDER BY branch;
OUTPUT
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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SELECT item,
       SUM(amount) / SUM(qty) AS avg_price
  FROM sales_line
 GROUP BY item
 ORDER BY item;
OUTPUT
       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/
Checkrun as lab_a
SELECT item, SUM(qty) AS total_qty, SUM(amount) AS total_amount
  FROM sales_line
 GROUP BY item
 ORDER BY item;
OUTPUT
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.

After the fixrun as lab_a
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;
OUTPUT
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
Setuprun as lab_a
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;
/
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.


Function created.
Reproducerun as lab_a
SELECT avg_salary(10) AS d10, avg_salary(30) AS d30 FROM dual;
OUTPUT
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/
Fixrun as lab_a
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;
/
OUTPUT
Function created.

In PL/SQL, the ORA-06512 line number tells you which division failed. Start there.

After the fixrun as lab_a
SELECT avg_salary(10) AS d10, avg_salary(30) AS d30 FROM dual;
OUTPUT
       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

Related errors