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.
Related Oracle documentation.
Possibly related:
- Previously Undocumented LNNVL SQL Function Buggy
- Cool Undocumented SQL Function: REVERSE
- Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives
- Cool SQL function: EXTRACT
- KeePass Password Safe
Tagged function, sql, undocumented | Post a Comment
Hope everything is working out...
“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.”
I think ’safely’ may be going a little to far. I’d be wary about using any feature prior to the version in which it was documented, as there may have been reasons why it was left undocumented.
May 2nd, 2006, at 4:35 pm #In this case, my main concern would be whether the CBO is able to correctly determine costs/selectivity/cardinality for that condition. I’d probably have reservations about using it with ‘exotic’ datatypes too.
Gary, you have valid concerns. The advice has always been to avoid using undocumented features.
The fact that LNNVL has been internally used by the Oracle optimizer (shows up in the query plan in certain situations) in versions prior to 10g, and that LNNVL has been officially documented in 10gR2, make it less of a concern (to me at least) if I want to use it in 9i for example, especially when I know that I will upgrade to 10gR2 in the near future.
May 2nd, 2006, at 5:01 pm #I did see it in the 10.1 docs. Oddly enough the Super Oracle search engine at tahiti didn’t find it when searching across all docs. Drilling into the 10.1 link and searching did reveal to docs.
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/functions068.htm#i1479295
May 3rd, 2006, at 11:40 am #Chuck, thanks for digging it up in 10.1.
I thought that the “search across the documentation libraries” should have returned results from 10.1 also, but it looks like this is not the case!
I have updated the post accordingly. Thanks again.
May 3rd, 2006, at 11:56 am #Now i wait for an article about the spivns() undocumented oracle function. It starts a space invaders clone. And guess the unlimited dbms_output buffering introduced in 10.2 was the base for it!
Karl
May 3rd, 2006, at 12:31 pm #I would not even pretend it is safe to use in 10g !
SQL> select * from dual where not lnnvl(1=1);
September 26th, 2006, at 2:52 am #select * from dual where not lnnvl(1=1)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel