PL/SQL Exponentiation

(Tested on Oracle 11.) You can use ** in PL/SQL to do exponentiation: 

SQL> set serveroutput on
SQL> declare
  2  a number := 8**0.5;
  3  begin
  4  dbms_output.put_line('a = '||a);
  5  end;
  6  /
a = 2.82842712474619009760337744841939615716

PL/SQL procedure successfully completed.

SQL>

The order of precedence seems strange as exponentiation is done before unary minus. I think that -2**2 should be 4, not -4. Of course, you can use brackets to change the order of precedence, if you wish:

SQL> declare
  2  b number := -2**2;
  3  c number := (-2)**2;
  4  begin
  5  dbms_output.put_line('b = '||b);
  6  dbms_output.put_line('c = '||c);
  7  end;
  8  /
b = -4
c = 4

PL/SQL procedure successfully completed.

SQL>

Oracle Precedence

This is a hypothetical example and, contrary to what I say below, I have nothing against French or German cars. I wanted to buy a new car so I made up a shortlist of manufacturers and put them in a table:

SQL> create table short_list
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into short_list values ('BMW')
  2  /

1 row created.

SQL> insert into short_list values ('Renault')
  2  /

1 row created.

SQL> insert into short_list values ('Subaru')
  2  /

1 row created.

SQL>

Then I decided I did not want to buy a French car so I made a list of French manufacturers and put them in a second table: 
 
SQL> create table french
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into french values ('Citroen')
  2  /

1 row created.

SQL> insert into french values ('Renault')
  2  /

1 row created.

SQL>

Then I decided I did not want to buy a German car either so I made a list of German manufacturers and put them in a third table:

SQL> create table german
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into german values ('BMW')
  2  /

1 row created.

SQL> insert into german values ('Mercedes')
  2  /

1 row created.

SQL>

Then I decided to see which manufacturers were still in my short list. My first attempt did not produce the result I expected. I think Oracle evaluated the not in before the union so the German manufacturers were still in the list:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  (select manufacturer from french)
  4  union
  5  (select manufacturer from german)
  6  /

MANUFACTURER
------------
BMW
Mercedes
Subaru
 
SQL>

Swapping the select statements before and after the union confirmed that the problem was caused by Oracle's precedence rules. This time, the French manufacturers were still in the list:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  (select manufacturer from german)
  4  union
  5  (select manufacturer from french)
  6  /

MANUFACTURER
------------
Citroen
Renault
Subaru

SQL>

To get the correct result I had to surround the select statements before and after the union with an extra set of brackets. This told Oracle to evaluate the union first:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  ((select manufacturer from french)
  4    union
  5  (select manufacturer from german))
  6  /

MANUFACTURER
------------
Subaru

SQL>