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 LNNVL could generate the error ORA-03113: end-of-file on communication channel (tested on Oracle XE):
SQL> select * from dual where not lnnvl(1=1);
select * from dual where not lnnvl(1=1)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> select * from dual;
ERROR:
ORA-03114: not connected to ORACLE
It looks like a bug to me.
Related articles:
- Undocumented LNNVL SQL function now safe to use
- Cool Undocumented SQL Function: REVERSE
- Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives
It is not possible for that to be a bug, you see this function does not exist as far as you are concerned. It works when and where it is needed internally.
The foibles of “undocumented”, recommend ignoring the man behind the curtain – the way this function works could change from release to release, patch to patch.
But this function is documented (as of 10gR1).
Indeed, learned something new
testing on sql*plus and production database :
SQL*Plus: Release 9.2.0.1.0 – Production on Vie Oct 20 13:29:46 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Conectado a: Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production JServer Release 9.2.0.4.0 – Production
SQL> SQL> SQL> SQL> select * from dual where not lnnvl(1=1) 2 / select * from dual where not lnnvl(1=1) * ERROR en lÃnea 1: ORA-03113: fin de archivo en el canal de comunicación
SQL> select * from dual 2 / select * from dual * ERROR en lÃnea 1: ORA-03114: no conectado a ORACLE
It seem like a bug , no ? 8))