Posts Tagged ‘undocumented’

Previously Undocumented LNNVL SQL Function Buggy

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

Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives

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:

[…]

Cool Undocumented SQL Function: REVERSE

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

About The BUILTIN, FIPSFLAG and INTERFACE Pragmas in Oracle

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

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