# How To Use CEIL In Oracle SQL

## July 29, 2021 | ## Oracle DBA |

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