In Oracle, the CEIL()
function returns the smallest integer that is greater than or equal to its argument.
Syntax
The syntax goes like this:
CEIL(n)
Where n
can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
Example
Here’s an example:
SELECT CEIL(7.15)
FROM DUAL;
Result:
CEIL(7.15) _____________ 8
Passing a negative value produces the following result:
SELECT CEIL(-7.15)
FROM DUAL;
Result:
CEIL(-7.15) ______________ -7
Compared to ROUND()
The CEIL()
function is different to the ROUND()
function. The ROUND()
function would round the number down in some instances, whereas CEIL()
always returns the smallest integer value is greater than or equal to its argument.
SELECT
CEIL(7.15),
ROUND(7.15)
FROM DUAL;
Result:
CEIL(7.15) ROUND(7.15) _____________ ______________ 8 7
Also, ROUND()
allows us to specify the number of decimal places to round to:
SELECT
CEIL(7.15),
ROUND(7.15, 1)
FROM DUAL;
Result:
CEIL(7.15) ROUND(7.15,1) _____________ ________________ 8 7.2
Non-Numeric Argument
The argument can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
Here’s what happens when we provide a non-numeric argument that can’t be converted to a numeric data type:
SELECT CEIL('Oops!')
FROM DUAL;
Result:
Error starting at line : 1 in command - SELECT CEIL('Oops!') FROM DUAL Error report - ORA-01722: invalid number
Null Values
Passing null
to CEIL()
returns null
:
SET NULL 'null';
SELECT CEIL(null)
FROM DUAL;
Result:
CEIL(NULL) _____________ null
By default, SQLcl and SQL*Plus return a blank space whenever null
occurs as a result of a SQL SELECT
statement.
However, you can use SET NULL
to specify a different string to be returned. Here I specified that the string null
should be returned.
Incorrect Argument Count
Calling CEIL()
without passing any arguments returns an error:
SELECT CEIL()
FROM DUAL;
Result:
Error starting at line : 1 in command - SELECT CEIL() FROM DUAL Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-00909: invalid number of arguments 00909. 00000 - "invalid number of arguments" *Cause: *Action:
And passing the wrong number of arguments results in an error:
SELECT CEIL(1, 2)
FROM DUAL;
Result:
Error starting at line : 1 in command - SELECT CEIL(1, 2) FROM DUAL Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-00909: invalid number of arguments 00909. 00000 - "invalid number of arguments" *Cause: *Action:
Oracle also has a FLOOR()
function that returns the largest integer equal to or less than its argument.