Conditional function LNNVL: the bullsh*t detector

The Oracle conditional function LNNVL is one of those urban-myths everyone likes. It has been around for years – it’s birth date is actually another mystery, some say Oracle 7.3 others 7.2, but rumor has it that it might had been around since PL/SQL creation.

But if you go around the docs of those times, the LNNVL function it’s not documented until Oracle 10g, when it sneaked into the official books of Oracle Documentation. Another fact to add more fog into the midst is that it’s said that this function was used internally until it became public in Oracle 10g.

But what is so special about this function?

In one sentence: It’s a conditional boolean function that returns TRUE whenever the condition passed on as argument is unkown or FALSE.

In other words is a bullsh*t detector! If the condition is 100% sure thing, absolute true, the function LNNVL throws FALSE, otherwise will ring the bell and give out TRUE.

Beeing a boolean function you might think that you couldn’t use it in SQL but actually you can, but only in the WHERE clause, because it’s the only place that can handles boolean values.

Let’s give out two examples to enlight about the true usefullness of this function:

LNNVL Function in SQL

SQL> select '>> This is bull <<'
 2  from dual
 3  where null = null;

no rows selected

Hey fellows! Looks like null it’s not null! Where is the bull? It’s time to call in the BULLSH*T DETECTOR!

SQL> select '>> This is bull <<'
 2   from dual
 3   where LNNVL(null = null);  

'>>THISISBULL<<'
------------------
>> This is bull <<

Looks like it worked because LNNVL throws TRUE if the condition is FALSE (which is not the case) or UNKOWN. In this case the condition is UNKOWN simply because the NULL status it’s not comparable through the equality operator (“=”), but with the “IS” operator. Let’s proove it:

SQL> select '>> This is not bull this time <<' Result
 2  from dual
 3  where null is null;  

RESULT
--------------------------------
>> This is not bull this time <<

LNNVL Function in PL/SQL

In PL/SQL it’s easier to produce bull! Let’s use the classic example of a non-initialized variable and use it in the condition.

SQL> r
 1  declare
 2   mynum  number;
 3   intovar  number:=0;
 4  begin
 5   select 1 into intovar
 6   from dual
 7   where lnnvl(mynum=0);
 8   -- The Test
 9   if intovar = 1 then
 10      dbms_output.put_line('The Bullsh*t detector done it again! Damn! He is good!');
 11   else
 12      dbms_output.put_line('Looks fine but its not');
 13   end if;
 14* end;

The Bullsh*t detector done it again! Damn! He is good!

PL/SQL procedure successfully completed.

My deep thought on this: If you’re to use this function it’s because you’re expecting bull. Try to work out not getting bull at all!

LMC.

About these ads