Oracle CEIL Function: A Simple Guide

The CEIL function in Oracle SQL is a mathematical function that "rounds up" a number to the smallest integer that is greater than or equal to that number.

The name CEIL comes from "ceiling," which is a helpful way to remember it: it always goes up to the "ceiling," or the next whole number above it (unless the number is already a whole number).

What is the CEIL Function in Oracle?

The CEIL function takes a single number as an argument and returns the smallest whole number that is greater than or equal to it.

• CEIL(10.2) returns 11
• CEIL(10.9) also returns 11
• CEIL(10) returns 10 (because 10 is an integer)
• CEIL(-10.2) returns -10 (because -10 is greater than -10.2)

This is different from standard rounding. ROUND(10.2) would return 10, but CEIL(10.2) returns 11. It's used in any situation where you must round up, such as calculating the number of containers needed to hold items.

CEIL Function Syntax

The syntax for CEIL is very simple:

CEIL(n)

Let's break that down:

• n: The number (or numeric column) you want to round up.

Oracle CEIL Function Examples

Here are two practical examples of how to use CEIL.

Example 1: Rounding Up a Decimal with CEIL

This example shows the basic behavior of CEIL with a positive and a negative number.

Query:

SELECT 
  CEIL(14.3) AS "Ceil_Positive",
  CEIL(-14.3) AS "Ceil_Negative",
  CEIL(14) AS "Ceil_Integer"
FROM DUAL;

Result:

Ceil_Positive  Ceil_Negative  Ceil_Integer
-------------  -------------  ------------
           15            -14            14

Example 2: Calculating "Total Pages" using CEIL

Imagine you have a blog with 152 articles, and you want to display 10 articles per page. You can use CEIL to find the total number of pages needed.

152 / 10 = 15.2. If you just round, you'd get 15 pages, but that would leave 2 articles out. CEIL ensures you get the correct number.

Query:

SELECT 
  CEIL(152 / 10) AS "Total_Pages_Needed"
FROM DUAL;

Result:

Total_Pages_Needed
------------------
                16

This shows you need 16 pages to display all 152 articles.