LNNVL is a SQL function that takes a condition as an argument. It returns TRUE if the condition is FALSE or NULL. It returns FALSE if the condition is TRUE. It is available but not documented in Oracle database versions prior to 10gR1.

In his comment to my previous post about this function, Laurent demonstrated how […]

In Oracle, NULL does not equal NULL:

EDDIE@XE> SELECT ‘hi there’ 2 FROM DUAL 3 WHERE NULL = NULL;

no rows selected

But sometimes, if not most of the times, you do want the condition NULL = NULL to return true. For example:

[…]

Let’s say that you want to check whether a word or a sentence reads the same backward or forward, how do you do it in SQL? you use the REVERSE function. We’ll see an example of that below, but first, a warning. Unlike LNNVL, REVERSE is still an undocumented SQL function, which means that it […]

A few weeks ago I blogged about PRAGMAs in PL/SQL. As you all know, as of the current release of PL/SQL, we have 4 documented pragma directives: AUTONOMOUS_TRANSACTION, RESTRICT_REFERENCES, EXCEPTION_INIT and SERIALLY_REUSABLE.

Karl posted a comment to draw my attention to the fact that in the SYS.STANDARD PL/SQL package, Oracle uses three additional undocumented pragma directives: […]

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 […]