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.
5 comments:
This is nothing new. There has always been separation between Oracle's SQL and PL/SQL engine. Some functions only work in SQL. Unfortunately CASE is one of those functions as well.
I think I had the same issue with NVL2 a few weeks ago. I don't really see a reason why it's not available, but hey, I'm sure there is a good reason - or the developers where just lazy :-)
Neil, CASE is available in PL/SQL.
Patrick
Neil,
funnily enough i would't suffer too much for the absence of CASE, i mean, when i have complex conditions i think is much more readable the if-elsif-else-end structured clause of PL/SQL, but NVL2 is a handy function, so, may be this is no news for you but it could be for someone else and that's what this blog is all about.
Patrick,
in my wish list of built-ins i have a NOTNVL(val1,val2) function, that is:
if val1 is null then
return null;
else
return val2;
end if;
I have my own version of course and i use a lot in Apex, when i need to return a different value only if it is not null, as it happens for instance when you need to insert a parameter in the middle of a preformatted string and clearly you don't want to return the entire string if the parameter is missing.
Thanks
Flavio
Byte64, your NOTNVL function looks a lot like the built in COALESCE function. Give it a shot.
SnippetJoe,
unless i am misinterpreting the COALESCE documentation, COALESCE works like this:
COALESCE (expr1, expr2)
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
My NOTNVL is slightly different because it is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr2 ELSE null END
Right?
Bye,
Flavio
Post a Comment