Re: NVL2 in PL/SQL 10g

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 5 Feb 2007 05:39:27 -0800
Message-ID: <1170682767.496556.128990@j27g2000cwj.googlegroups.com>

On Feb 4, 4:58 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Daniel, I will disagree with your response in that if NVL2 was a valid PL/SQL function Klabu attemplted to use it correctly in an assignment statement. As Daniel is probably aware, but did not address in his response, most SQL functions also exist as PL/SQL functions and PL/SQL functions can be referenced in PL/SQL assignment statements. Unfortunately I am unaware of any separate list of the function available in PL/SQL or differences in how they work. There have been a couple of minor value issues over the releases between the value returned by a PL/SQL function and the same SQL function. Then again in the past Oracle PL/SQL and SQL used totally separate parsers so that some SQL features like CASE were not available to SQL issued from PL/SQL. Daniel is correct in pointing out that you normally place your functions in SQL statements and by doing so you might eliminate the need to perform the work in a PL/SQL statement. This is one method of using SQL Functions not available in PL/SQL.

I think that the ANSI standard COALESCE function can be used to return the first non-null value in this example if variable are used instead of constants in the problem you are really trying to solve.

UT1 > @t7
UT1 > set echo on
UT1 > DECLARE

  2 l_test VARCHAR2(30) := 'hi';
  3 BEGIN
  4 l_test := coalesce('hello','world','earth');   5 dbms_output.put_line(l_test);
  6 l_test := coalesce(NULL,'world','earth');   7 dbms_output.put_line(l_test);
  8 END;
  9 /
hello
world

PL/SQL procedure successfully completed.

The CASE statement or IF THEN ELSE structure could also be used in PL/ SQL code where several tests need to be done.

HTH -- Mark D Powell -- Received on Mon Feb 05 2007 - 07:39:27 CST