LN() Function in Oracle

In Oracle, the LN() function returns the natural logarithm of its argument, where the argument is greater than 0.

In other words, it returns the base-e logarithm of its argument.

The number e, also known as Euler’s number, is a mathematical constant approximately equal to 2.71828.

Syntax

The syntax goes like this:

LN(n)

Where n is 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 LN(73)
FROM DUAL;

Result:

                                    LN(73) 
__________________________________________ 
   4.2904594411483911290921088574385425709

Here’s what we get when we pass e:

SELECT LN(2.718281828459045235360287471352662497757)
FROM DUAL;

Result:

   LN(2.718281828459045235360287471352662497757) 
________________________________________________ 
                                               1

However, the number of fractional places makes a difference with this example. Here’s what happens when I remove the last digit:

SELECT LN(2.71828182845904523536028747135266249775)
FROM DUAL;

Result:

   LN(2.71828182845904523536028747135266249775) 
_______________________________________________ 
     0.9999999999999999999999999999999999999963

Negative Values

Negative values result in an “out of range” error:

SELECT LN(-5.490)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LN(-5.490)
FROM DUAL
Error report -
ORA-01428: argument '-5.49' is out of range

Zero

Passing 0 also results in an “out of range” error:

SELECT LN(0)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LN(0)
FROM DUAL
Error report -
ORA-01428: argument '0' is out of range

Expressions

The argument can include expressions like this:

SELECT LN(2 * 3)
FROM DUAL;

Result:

                                    LN(2*3) 
___________________________________________ 
   1.79175946922805500081247735838070227272

Non-Numeric Arguments

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 an example of what happens when the argument doesn’t satisfy that criteria:

SELECT LN('Euler')
FROM DUAL;

Result:

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

Null Arguments

LN() returns null if the argument is null:

SET NULL 'null';

SELECT LN(null)
FROM DUAL;

Result:

   LN(NULL) 
___________ 
       null 

By default, SQLcl and SQL*Plus return a blank space whenever a null value 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.

Missing Arguments

Calling LN() with the wrong number of arguments, or without any arguments results in an error:

SELECT LN()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LN()
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:

SELECT LN(10, 2)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LN(10, 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: