Loading...

Thursday, April 19, 2007

ORA-06550 and PLS-00201:identifier 'NVL2' must be declared

I must admit if was a bit surprised when i saw the following error this morning:

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:

neil said...

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.

Patrick Wolf said...

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

Byte64 said...

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

SnippetyJoe said...

Byte64, your NOTNVL function looks a lot like the built in COALESCE function. Give it a shot.

Byte64 said...

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

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles