NVL2 in PL/SQL 10g

[klabu]

<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

[DA Morgan]

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
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Mark D Powell]

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

[DA Morgan]

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

damo...@x.washington.edu

[klabu]

I *think* I have looked at NVL2 page on PSOUG site (which usually gets
1st-page google hit, as you may know)

Anyway I think, before I ran out of patience, and thinking to myself
"wow this is unbelievable !"
I looked at at least 10 sites from google result and Quest's "PL/SQL
Knowledgebase" and found

NOT ONE SINGLE one points out the simple truth that

*** NVL2 CAN NOT BE USED IN PL/SQL (ASSIGNMENT) ***

They ALL show the same old "SELECT NVL2(......) FROM........"

Maybe they all copied from each other in this case.

[DA Morgan]

I will update the PSOUG site today with a demo that shows this.

Thanks.