In Oracle, the POWER() function returns its first argument raised to the power of its second argument.

Syntax

The syntax goes like this:

POWER(n2, n1)

Each argument can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

Both arguments can be any number, however, if n2 is negative, then n1 must be an integer.

Example

Here’s an example to demonstrate:

SELECT POWER(8, 2) 
FROM DUAL;

Result:

   POWER(8,2) 
_____________ 
           64 

And here it is using a fractional part in the second argument:

SELECT POWER(8, 2.5) 
FROM DUAL;

Result:

                              POWER(8,2.5) 
__________________________________________ 
   181.01933598375616624661615669884135406 

However, using a fractional part is only possible when the first argument is positive.

Negative Values

As mentioned, if the first argument is negative, then the second argument must be an integer:

SELECT POWER(-8, 2) 
FROM DUAL;

Result:

   POWER(-8,2) 
______________ 
            64 

Here’s what happens when the second number isn’t an integer:

SELECT POWER(-8, 2.5) 
FROM DUAL;

Result:

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

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 POWER('Gosh', 'Dang')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT POWER('Gosh', 'Dang')
FROM DUAL
Error report -
ORA-01722: invalid number

Null Arguments

POWER() returns null if any argument is null:

SET NULL 'null';

SELECT 
    POWER(null, 3),
    POWER(8, null),
    POWER(null, null)
FROM DUAL;

Result:

   POWER(NULL,3)    POWER(8,NULL)    POWER(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 POWER() without any arguments results in an error:

SELECT POWER()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT POWER()
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 the same thing applies when calling it with too many arguments:

SELECT POWER(10, 2, 3)
FROM DUAL;

Result:

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