Re: NVL2 in PL/SQL 10g

klabu wrote:
> <10gR2>
>
> so is it a fact that NVL2 can NOT be used in PL/SQL ???
> Quite surprised to find.
> But was REALLY surprised that I can not find confirmation by googling
> (yet)
>
>
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
> Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> DECLARE
> 2 l_test VARCHAR2(30) := 'hi';
> 3 BEGIN
> 4 l_test := nvl2('hello','world','earth');
> 5 END;
> 6 /
> l_test := nvl2('hello','world','earth');
> *
> ERROR at line 4:
> ORA-06550: line 4, column 15:
> PLS-00201: identifier 'NVL2' must be declared
> ORA-06550: line 4, column 5:
> PL/SQL: Statement ignored

The problem is not PL/SQL it is how you are using it.

SQL> DECLARE
   2 c test.category%TYPE;
   3 i PLS_INTEGER;
   4 BEGIN
   5 SELECT category, SUM(NVL2(outval, -outval, inval))    6 INTO c, i
   7 FROM test
   8 WHERE rownum = 1
   9 GROUP BY category;
  10 END;
  11 /

PL/SQL procedure successfully completed.

SQL>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org