msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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 on 11 Oct 06 | Tags: , , ,


Reader's Comments

  1. |

    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.

  2. |

    But this function is documented (as of 10gR1).

  3. |

    Indeed, learned something new :)

  4. |

    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))