I know Power function works in Oracle; however I need to test double asterisk ( ** ) as Exponentiation Operators in Oracle PL/SQL.

select (2 **3) from dual; ---there is an error.

if I put it into PL/SQL block, still error: declare kkk number; begin select (2**3) into kkk from dual; end;

who can help me? thanks!

CodePudding user response：

select (2 **3) from dual; is not PL/SQL - that's plain SQL!

And begin select (2**3) into kkk from dual; end; is still SQL in PL/SQL.

You can use it, in pure PL/SQL see PL/SQL Language Fundamentals - Table 2-2 PL/SQL Delimiters

DECLARE
  res NUMBER;
BEGIN
  res := 2 ** 3;
  DBMS_OUTPUT.PUT_LINE(res);
END;

CodePudding user response：

No, the Oracle SQL arithmetic operators are:

• The unary operators and -, for positive and negative numbers; and
• The binary operators , -, * and /, for addition, subtraction, multiplication and division.

There is no exponentiation operator in Oracle SQL.

You can use the POWER(n2, n1) function to raise n2 to the power of n1.

SELECT POWER(2,3) FROM DUAL;

Outputs:

As described in @WernfriedDomscheit's answer, you can use the exponentiation operator in PL/SQL (but not if you switch from the PL/SQL scope back to the SQL scope with a SELECT/INSERT/UPDATE/MERGE statement).