How To Use CEIL In Oracle SQL

This tutorial is based on examples so it would be easier to understand. Oracle Ceil function allows to round a number up and the Ceil function removes all numbers after the decimal place and returns an integer. The syntax of Oracle Ceil is:

CEIL (<number>);

The following Oracle Ceil example has input value “11.11111” and it will round up the number to “12“. The Ceil function always removes all numbers after the decimal place.

 SELECT CEIL (11.11111)
   FROM dual;

When the values on left of the decimal point are all zeros then Oracle Ceil returns equal integer value as the same number being integer. The next example turns “11.00” to “11“.

 SELECT CEIL (11.00)
   FROM dual;

The Ceil function will round up even the smallest fraction of the number and in this case the input number is “11.00000001” and no any other rounding function beside Ceil will return number “12” from it.

 SELECT CEIL (11.00000001)
   FROM dual;

The third example’s value “11.9999999” will become “12” even with the Oracle Round function and the example demonstrates that there is no exceptions in Oracle Ceil.

 SELECT CEIL (11.9999999)
   FROM dual;

This example is about how to round up using Oracle Ceil with keeping the decimal places in number. First we do know Ceil returns only an integer value, so we need to move decimal place to a lower position before applying function Ceil. The following example we would like to keep 3 numbers after a decimal point and we will multiply the input number “11.11111” with “1000“.

 SELECT 11.11111 * 1000
   FROM dual;

Now the number has become “11111.11” and it is time to apply Oracle Ceil on it.

 SELECT CEIL (11111.11)
   FROM dual;

To get the numbers after decimal point back we need to divide the last integer (11112) with “1000“.

 SELECT 11112 / 1000
   FROM dual;

The example above is a workaround to do round up and to keep the decimal places. The output on the last example cannot be achieved using Oracle Round because as it does not round up. Take a look at the following example.

 SELECT ROUND(11.11111,3)
  FROM dual;

As you see on the output it didn’t become 11.112 instead it is 11.111.

To round up or down numbers take a look at the links below. To round down and to receive an integer value use Oracle Floor or Oracle Trunc and to normal rounding use Oracle Round function.

See Also:
Oracle Select Oracle Round Oracle Trunc Home