SQL Function - Nvl2

[Gustavo]

There is no template for the Nvl2 SQL function.
Also, when you type "Nvl2(" in a SQL window, the parameter list is not shown.

 

[Marco Kalter]

There is no template for the Nvl2 SQL function.

We'll add it. You can also add it yourself of course.

Also, when you type "Nvl2(" in a SQL window, the parameter list is not shown.

Standard SQL functions are described from the sys.standard package. Apparently nvl2 is not specified there.

 

[Gustavo]

We'll add it. You can also add it yourself of course.

Of Course...

Standard SQL functions are described from the sys.standard package. Apparently nvl2 is not specified there.

Does anyone know where it comes from?

 

[Worker]

NVL2 in Oracle 8i and 9i is a SQL-only function. It is not supported in PL/SQL, and not present in the sys.standard package. In 9i and above you can use it in SQL statements inside of PL/SQL. In 8i you can't, because 8i has separate SQL parsers for SQL and PL/SQL.

I don't know if it was ever added to PL/SQL (as in "s := NVL2('a','b','c')").

EDIT: Oracle docs for 10.2 don't mention NVL2 as a PL/SQL function:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref619

 

[Gustavo]

Originally posted by Worker:
I don't know if it was ever added to PL/SQL (as in "s := NVL2('a','b','c')").

Can someone test this in 10g?

declare
  a varchar2(10);
begin
  a := nvl2(null,'not null', 'null');
  dbms_output.put_line(a);
end;
/

 

[Marco Kalter]

Oracle 10.2:

ORA-06550: line 4, column 8:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

 

[Gustavo]

Same as 9i.

Anyway, I'll try to find this function's source.