In Oracle, the ROUND(number) function returns a number rounded to a given number of decimal places.

By default, it rounds the number to zero decimal places, but you can provide an optional argument that specifies the number of decimal places to use.

Oracle also has a ROUND(date) syntax, which is used on dates. This article is solely about the ROUND(number) syntax, which is used on numbers.

Syntax

The syntax goes like this:

ROUND(n [, integer ])

Where n can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, and integer is an optional integer that specifies the number of decimal places to round n to.

Example

Here’s an example:

SELECT ROUND(7.85)
FROM DUAL;

Result:

   ROUND(7.85) 
______________ 
             8 

Negative Value

Switching it to a negative value produces the following result:

SELECT ROUND(-7.85)
FROM DUAL;

Result:

   ROUND(-7.85) 
_______________ 
             -8

Second Argument

Here’s an example of providing a second argument:

SELECT ROUND(7.85, 1)
FROM DUAL;

Result:

   ROUND(7.85,1) 
________________ 
             7.9

In this case, I specified that the result should be rounded to one decimal place.

Here’s another example with a larger number of decimal places:

SELECT ROUND(3.14159265359, 4)
FROM DUAL;

Result:

   ROUND(3.14159265359,4) 
_________________________ 
                   3.1416

Negative Rounding

You can also specify a negative value for the second argument. Doing so rounds the number off to the left of the decimal point:

SELECT ROUND(75631, -3)
FROM DUAL;

Result:

   ROUND(75631,-3) 
__________________ 
             76000

Rounding Non-Numeric Arguments

Here’s what happens when we try to round a non-numeric argument that can’t be converted to a numeric data type:

SELECT ROUND('Bruce')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ROUND('Bruce')
FROM DUAL
Error report -
ORA-01722: invalid number

However, we can round dates – there’s a date version of this function that allows us to round dates.

Null Values

Trying to round null returns null, and trying to round a number by null also results in null:

SET NULL 'null';

SELECT 
    ROUND(null),
    ROUND(null, 2),    
    ROUND(2.345, null)
FROM DUAL;

Result:

   ROUND(NULL)    ROUND(NULL,2)    ROUND(2.345,NULL) 
______________ ________________ ____________________ 
          null             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 ROUND() without passing any arguments returns an error:

SELECT ROUND()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ROUND()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT ROUND(1.34, 2, 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ROUND(1.34, 2, 3)
FROM DUAL
Error at Command Line : 1 Column : 23
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: