DISQUS

Eddie Awad’s Blog: Undocumented LNNVL SQL function now safe to use

  • Gary · 3 years ago

    "So, if you’re stuck with Oracle 8i or 9i, you can safely use the undocumented LNNVL function, knowing that it has become documented and supported in 10g."


    I think 'safely' may be going a little to far. I'd be wary about using any feature prior to the version in which it was documented, as there may have been reasons why it was left undocumented.
    In this case, my main concern would be whether the CBO is able to correctly determine costs/selectivity/cardinality for that condition. I'd probably have reservations about using it with 'exotic' datatypes too.

  • Eddie Awad · 3 years ago

    Gary, you have valid concerns. The advice has always been to avoid using undocumented features.


    The fact that LNNVL has been internally used by the Oracle optimizer (shows up in the query plan in certain situations) in versions prior to 10g, and that LNNVL has been officially documented in 10gR2, make it less of a concern (to me at least) if I want to use it in 9i for example, especially when I know that I will upgrade to 10gR2 in the near future.

  • Chuck · 3 years ago

    I did see it in the 10.1 docs. Oddly enough the Super Oracle search engine at tahiti didn't find it when searching across all docs. Drilling into the 10.1 link and searching did reveal to docs.


    http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/functions068.htm#i1479295

  • Eddie Awad · 3 years ago

    Chuck, thanks for digging it up in 10.1.


    I thought that the "search across the documentation libraries" should have returned results from 10.1 also, but it looks like this is not the case!


    I have updated the post accordingly. Thanks again.

  • Karl · 3 years ago

    Now i wait for an article about the spivns() undocumented oracle function. It starts a space invaders clone. And guess the unlimited dbms_output buffering introduced in 10.2 was the base for it!
    ;-)
    Karl

  • Laurent Schneider · 3 years ago

    I would not even pretend it is safe to use in 10g !


    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