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.

Filed in Oracle, Tips with 4 Comments | Tags: bug, function, sql, undocumented

---