CEIL IN ORACLE - Online Tech Support

This tutorial is based on examples to be easier to follow. The Oracle Ceil function allows rounding a number value up and the function removes all numbers after the decimal place. The Oracle Ceil syntax is following:

CEIL (<number>);

The first Oracle Ceil example has input value 11.11111 and it rounds the number up and returns an integer value (number 12). The Ceil function always removes all numbers after a decimal place.

 SELECT CEIL (11.11111)
   FROM dual;

When the numbers after the decimal place will remain all zeros then Oracle Ceil returns equal integer value and on the following example it is number 11. Have a look at the example below.

 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. No any other rounding function beside Ceil will return number 12 with it.

 SELECT CEIL (11.00000001)
   FROM dual;

The following example will be round to 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;

The next 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 lower position before applying function Ceil. The following example we would like to keep 3 numbers after a decimal place and we will multiply the input number 11.11111 with 1000.

 SELECT 11.11111 * 1000
   FROM dual;

Now it is time to apply Oracle Ceil on 11111.11.

 SELECT CEIL (11111.11)
   FROM dual;

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

 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 a number 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.