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: