Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
 Oracle numeric functions

# Single row numeric functions

Oracle provides a lot of standard numeric functions for single rows. Here is a list of all the single row numeric functions (in version 10.2).
Function Usage Description
ABS ABS(n) ABS returns the absolute value of n.
ACOS ACOS(n) ACOS returns the arc cosine of n. The argument n must be in the range of -1 to 1, and the function returns a value in the range of 0 to pi, expressed in radians.
ASIN ASIN(n) ASIN returns the arc sine of n. The argument n must be in the range of -1 to 1, and the function returns a value in the range of -pi/2 to pi/2, expressed in radians.
ATAN ATAN(n) ATAN returns the arc tangent of n. The argument n can be in an unbounded range and returns a value in the range of -pi/2 to pi/2, expressed in radians.
ATAN2 ATAN2(n1,n2) or ATAN2(n1/n2) ATAN2 returns the arc tangent of n1 and n2. The argument n1 can be in an unbounded range and returns a value in the range of -pi to pi, depending on the signs of n1 and n2, expressed in radians. ATAN2(n1,n2) is the same as ATAN2(n1/n2).
BITAND BITAND(expr1,expr2) BITAND computes an AND operation on the bits of expr1 and expr2, both of which must resolve to nonnegative integers, and returns an integer. This function is commonly used with the DECODE function, as illustrated in the example that follows.
CEIL CEIL(n) This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
COS COS(n) COS returns the cosine of n (an angle expressed in radians). This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.
COSH COSH(n) COSH returns the hyperbolic cosine of n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.
EXP EXP(n) EXP returns e raised to the nth power, where e = 2.71828183... The function returns a value of the same type as the argument. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.
FLOOR FLOOR(n) FLOOR returns largest integer equal to or less than n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
LN LN(n) LN returns the natural logarithm of n, where n is greater than 0. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.
LOG LOG(n2,n1) LOG returns the logarithm, base n2, of n1. The base n1 can be any positive value other than 0 or 1 and n2 can be any positive value. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If any argument is BINARY_FLOAT or BINARY_DOUBLE, then the function returns BINARY_DOUBLE. Otherwise the function returns NUMBER.
MOD MOD(n2,n1) MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
NANVL NANVL(n2,n1) The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. It instructs Oracle Database to return an alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2. This function is useful for mapping NaN values to NULL. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
POWER POWER(n2,n1) POWER returns n2 raised to the n1 power. The base n2 and the exponent n1 can be any numbers, but if n2 is negative, then n1 must be an integer. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If any argument is BINARY_FLOAT or BINARY_DOUBLE, then the function returns BINARY_DOUBLE. Otherwise the function returns NUMBER.
REMAINDER REMAINDER(n2,n1) REMAINDER returns the remainder of n2 divided by n1. This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype. The MOD function is similar to REMAINDER except that it uses FLOOR in its formula, whereas REMAINDER uses ROUND.
ROUND (number) ROUND (n) ROUND(n, integer) ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point. n can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The argument integer must be an integer. If you omit integer, then the function returns the same datatype as the numeric datatype of the argument. If you include integer, then the function returns NUMBER. For NUMBER values, the value n is rounded away from 0 (for example, to x+1 when x.5 is positive and to x-1 when x.5 is negative). For BINARY_FLOAT and BINARY_DOUBLE values, the function rounds to the nearest even value. Please refer to the examples that follow.
SIGN SIGN(n) SIGN returns the sign of n. This function takes as an argument any numeric datatype, or any nonnumeric datatype that can be implicitly converted to NUMBER, and returns NUMBER. of NUMBER type, the sign is:
-1 if n<0
0 if n=0
1 if n>0

For binary floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE), this function returns the sign bit of the number. The sign bit is:
-1 if n<0
+1 if n>=0 or n=NaN
SIN SIN(n) SIN returns the sine of n (an angle expressed in radians). This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.
SINH SINH(n) SINH returns the hyperbolic sine of n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.
SQRT SQRT(n) SQRT returns the square root of n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
TAN TAN(n) TAN returns the tangent of n (an angle expressed in radians). This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.
TANH TANH(n) TANH returns the hyperbolic tangent of n. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If the argument is BINARY_FLOAT, then the function returns BINARY_DOUBLE. Otherwise the function returns the same numeric datatype as the argument.
TRUNC (number) TRUNC(n1) TRUNC(n1,n2) The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point. This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you omit n2, then the function returns the same datatype as the numeric datatype of the argument. If you include n2, then the function returns NUMBER.
WIDTH_BUCKET WIDTH_BUCKET(expr,min_value, max_value,num_buckets) WIDTH_BUCKET lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. (Compare this function with NTILE, which creates equiheight histograms.) Ideally each bucket is a closed-open interval of the real number line. For example, a bucket can be assigned to scores between 10.00 and 19.999... to indicate that 10 is included in the interval and 20 is excluded. This is sometimes denoted [10, 20).
For a given expression, WIDTH_BUCKET returns the bucket number into which the value of this expression would fall after being evaluated.
expr is the expression for which the histogram is being created. This expression must evaluate to a numeric or datetime value or to a value that can be implicitly converted to a numeric or datetime value. If expr evaluates to null, then the expression returns null.
min_value and max_value are expressions that resolve to the end points of the acceptable range for expr. Both of these expressions must also evaluate to numeric or datetime values, and neither can evaluate to null.
num_buckets is an expression that resolves to a constant indicating the number of buckets. This expression must evaluate to a positive integer.