Undocumented LNNVL SQL function now safe to use

Connect to your Oracle database and try the following:

SELECT 'hi there' AS mycol
 FROM DUAL
 WHERE lnnvl (1 = 2) 

You should get :

 MYCOL 
 ---------- 
 hi there 

Now, you ask, what is LNNVL? You search the Oracle documentation, but you cannot find any mention of it in releases prior to 10gR1, yet you successfully execute the above query in your Oracle 9.2 (or even 8.1.7) database!

LNNVL had been an undocumented SQL function until Oracle database version 10gR1. Let’s go back in time a bit and read what Jonathan Lewis and Lex de Haan wrote about it:

From: Jonathan Lewis
Date: Sun, Nov 2 2003 10:18 am
To: comp.databases.oracle.server
Message:

lnnvl(predicate) is true if predicate is false or null. I can’t say "for sure", but I believe it appeared somewhere around 7.3, or maybe 7.2 to handle problems arising from the SQL generated by a query co-ordinator for its parallel query slaves when Oracle was trying to get partition elimination on partition views. (And if it really is there for PQ problems, I’d second Richard’s advice – don’t use it, you never know when it’s going to disappear).

 

From: Lex de Haan
Date: Sat, 29 May 2004 21:27:53
To: oracle-l
Message:

The ANSI/ISO SQL standard offers the three operators you need: "IS TRUE", "IS FALSE", and "IS UNKNOWN". But as far as I know, no vendor has implemented these. Note the power of these operators; they accept a Boolean argument. The "IS NULL" becomes very clumsy as soon as multiple predicates are involved. One nice Oracle function (that has been around for quite a while, but was undocumented until 10g) is the LNNVL function.

The Oracle’s LNNVL function:

• Is similar to the SQL/Foundation:2003 standard’s IS NOT TRUE (or IS FALSE).
• Is not documented in Oracle database versions prior to 10gR1.
• Provides a concise way to evaluate a condition when one or both operands of the condition may be null.
• Can be used only in the WHERE clause of a query.
• Takes a condition as an argument. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.
• Returns TRUE if the condition is FALSE or UNKNOWN (NULL).
• Returns FALSE if the condition is TRUE.
• Is sometimes used internally by the Oracle Database to rewrite queries.

So, if you’re stuck with Oracle 8i or 9i, you can safely use the undocumented LNNVL function, knowing that it has become documented and supported in 10g and above.

Related Oracle documentation.

6 Comments | Filed in Oracle | Tags: function, sql, undocumented

---