LOG() Function in Oracle

In Oracle, the LOG() function returns the logarithm, base n2, of n1, where n2 is the first argument and n1 is the second.

Syntax

The syntax goes like this:

LOG(n2, n1)

Where n2 can be any positive value other than 0 or 1, and  n1 is any positive value.

Example

Here’s an example:

SELECT LOG(16, 73)
FROM DUAL;

Result:

                                 LOG(16,73) 
___________________________________________ 
   1.54745613972000430746600429203996036707

Negative Values

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

SELECT LOG(-16, 73)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LOG(-16, 73)
FROM DUAL
Error report -
ORA-01428: argument '-16' is out of range

Same for the second argument:

SELECT LOG(16, -73)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LOG(16, -73)
FROM DUAL
Error report -
ORA-01428: argument '-73' is out of range

Zero

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

SELECT LOG(0, 73)
FROM DUAL;

Result:

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

And again, the same applies to the second argument:

SELECT LOG(16, 0)
FROM DUAL;

Result:

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

Passing 1

Passing 1 for the second argument results in 0:

SELECT LOG(16, 1)
FROM DUAL;

Result:

   LOG(16,1) 
____________ 
           0

Passing 1 for the first argument results in an “out of range” error:

SELECT LOG(1, 2)
FROM DUAL;

Result:

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

Expressions

The arguments can include expressions like this:

SELECT LOG(10 + 6, 12 * 30)
FROM DUAL;

Result:

                            LOG(10+6,12*30) 
___________________________________________ 
   2.12296327408241867769444932934625579835 

Non-Numeric Arguments

The arguments 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 arguments don’t satisfy that criteria:

SELECT LOG('Homer', 'Symptom')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LOG('Homer', 'Symptom')
FROM DUAL
Error report -
ORA-01722: invalid number

Null Arguments

LOG() returns null if any argument is null:

SET NULL 'null';

SELECT 
    LOG(null, 1024),
    LOG(16, null),
    LOG(null, null)
FROM DUAL;

Result:

   LOG(NULL,1024)    LOG(16,NULL)    LOG(NULL,NULL) 
_________________ _______________ _________________ 
             null            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 LOG() with the wrong number of arguments, or without any arguments results in an error:

SELECT LOG()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LOG()
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 LOG(10, 2, 3)
FROM DUAL;

Result:

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