in 8i and before plsql had its parser, the database had its parser and so on.  What this lead to was this behaviour at times:

ops$tkyte@ORA817DEV> select count(*) over () from dual;
 
COUNT(*)OVER()
--------------
             1
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> begin
  2      for x in ( select count(*) over () from dual )
  3      loop
  4          null;
  5      end loop;
  6  end;
  7  /
    for x in ( select count(*) over () from dual )
                                    *
ERROR at line 2:
ORA-06550: line 2, column 37:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from
 
<b>we know that sql is valid but the plsql parser hadn't yet caught up to this new feature, the workaround was:</b>
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          type rc is ref cursor;
  3          C rc;
  4          l_cnt number;
  5  begin
  6          open c for 'select count(*) over () from dual';
  7          loop
  8                  fetch c into l_cnt;
  9                  exit when c%notfound;
 10          end loop;
 11          close c;
 12  end;
 13  /
 
PL/SQL procedure successfully completed.


<b>to use dynamic sql to hide the construct from plsql and let the rdbms parse the query itself.  In 9i, with the common parser (eg: sharing code), this is corrected for once and for all</b>

 

it was documented in the 9iR2 doc set....

ops$tkyte@ORA9IR2> select nvl2( '', 'a', 'b' ) from dual;

N
-
b

ops$tkyte@ORA9IR2> exec dbms_output.put_line(  nvl2( '', 'a', 'b' )  );
BEGIN dbms_output.put_line(  nvl2( '', 'a', 'b' )  ); END;

                             *
ERROR at line 1:
ORA-06550: line 1, column 30:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


ops$tkyte@ORA9IR2> begin for x in ( select nvl2('','a','b') x from dual )
  2  loop
  3  dbms_output.put_line( x.x );
  4  end loop;
  5  end;
  6  /
b

PL/SQL procedure successfully completed.


looks like in plsql, you cannot call it "directly", but like decode - must "select it"