Thursday, July 28, 2011

Division by Zero

This was tested on an Oracle 9 database. Division by zero is not allowed in SQL. If you try it, you get an ORA-01476:

SQL> SELECT 1/0 FROM DUAL
  2  /
SELECT 1/0 FROM DUAL
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>


It is not allowed in PL/SQL either:

SQL> DECLARE
  2   QUOTIENT NUMBER;
  3  BEGIN
  4   QUOTIENT := 1 / 2;
  5   DBMS_OUTPUT.PUT_LINE('Quotient 1 = '||QUOTIENT);
  6   QUOTIENT := 1 / 0;
  7   DBMS_OUTPUT.PUT_LINE('Quotient 2 = '||QUOTIENT);
  8  END;
  9  /
Quotient 1 = .5
DECLARE
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 6

SQL>


Fortunately you can trap this error tidily as follows:

SQL> DECLARE
  2   QUOTIENT NUMBER;
  3  BEGIN
  4   QUOTIENT := 1 / 2;
  5   DBMS_OUTPUT.PUT_LINE('Quotient 1 = '||QUOTIENT);
  6   QUOTIENT := 1 / 0;
  7   DBMS_OUTPUT.PUT_LINE('Quotient 2 = '||QUOTIENT);
  8  EXCEPTION
  9   WHEN ZERO_DIVIDE THEN
 10   DBMS_OUTPUT.PUT_LINE('Division by zero not allowed');
 11  END;
 12  /
Quotient 1 = .5
Division by zero not allowed

PL/SQL procedure successfully completed.

SQL>

No comments: