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>

FLOOR and CEIL

FLOOR returns the smallest integer less than or equal to the expression in brackets. CEIL returns the smallest integer greater than or equal to the expression in brackets. I tried to catch them out but failed:

SQL> select floor(-3.2), ceil(-3.2)

  2  from dual;

FLOOR(-3.2) CEIL(-3.2)

----------- ----------

         -4         -3

1 row selected.

SQL> select floor(-0), ceil(-0)

  2  from dual;

 FLOOR(-0)   CEIL(-0)

---------- ----------

         0          0

1 row selected.

SQL> select floor(0/0) from dual;

select floor(0/0) from dual

              *

ERROR at line 1:

ORA-01476: divisor is equal to zero
  

SQL> select ceil(0/0) from dual;

select ceil(0/0) from dual

             *

ERROR at line 1:

ORA-01476: divisor is equal to zero
  

SQL> select floor(0/1), ceil(0/1)

  2  from dual;

FLOOR(0/1)  CEIL(0/1)

---------- ----------

         0          0

1 row selected.

SQL> select floor(2.5*2.5), ceil(2.5*2.5)

  2  from dual;

FLOOR(2.5*2.5) CEIL(2.5*2.5)

-------------- -------------

             6             7

1 row selected.

SQL> select floor(12.0), ceil(12.0)

  2  from dual;

FLOOR(12.0) CEIL(12.0)

----------- ----------

         12         12

1 row selected.

SQL> select floor(14.6), ceil(14.6)

  2  from dual;

FLOOR(14.6) CEIL(14.6)

----------- ----------

         14         15

1 row selected.

SQL>