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: