Re: NVL2 in PL/SQL 10g

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 05 Feb 2007 10:04:45 -0800
Message-ID: <1170698684.763845@bubbleator.drizzle.com>

Mark D Powell wrote:

> 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.

You are correct and I should have been clearer. I was too focused on the words "can NOT."

Someday, given the time, I'd like to test every Oracle function and add to the PSOUG website the list of which ones are not PL/SQL functions.

Unless, of course, Mark beats me too it by having his team merge them so that the distinction disappears.

-- 
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

Received on Mon Feb 05 2007 - 12:04:45 CST