1 error has occurred
ORA-06550: line 4, column 3: PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 2, column 1: PL/SQL: Statement ignored
What's wrong with NVL2?!?!
Well, i tried using NVL2 as a PL/SQL expression rather than inside a SQL statement.
my_var := NVL2(old_value, new_value_if_not_null, new_value_if_null);
The fact is that there is a bunch of built-in functions like DECODE that have no counterpart in PL/SQL.
And Oracle documentation is a bit naive on this subject because it gives a list of built-in PL/SQL functions and says "refer to the SQL reference for a description of other functions", but it doesn't say "there is no NVL2 function", you must derive this rule looking up the desired function name in the list. Actually i found a paragraph where it mentions the absence of DECODE and others, but it doesn't mention NVL2.
In oracle SQL terms is if we had two tables containing the built-in functions accepted by SQL and built-ins accepted by PL/SQL, and we had to run a special query using set operators to find out what's missing.
select function_name
from sql_functions
minus
select function_name
from plsql_functions;
function_name
---------------
DECODE
DUMP
NVL2
...
Who knows why they forgot to include NVL2 in the PL/SQL built-ins.
