01 November 2010

A better alternative to NVL2 (29 October quiz) (1565)

The 29 October quiz tested your awareness of the NVL2 function (which works quite differently from NVL), and the fact that it is available only in SQL, not yet in the PL/SQL language. The quiz offered several options that served, in effect, as alternatives to NVL2, from within PL/SQL. Vitaliy wrote to suggest that I had missed a simpler and more elegant alternative: CASE. So just in case you would like to use it.... 
CREATE OR REPLACE FUNCTION NVL2 (val         IN VARCHAR2
                              , ifnotnull   IN VARCHAR2
                              , ifnull      IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  RETURN CASE WHEN val IS NOT NULL THEN ifnotnull ELSE ifnull END;
END;
/
Posted by at

2 comments:

  1. Kim Berg Hansen01 November, 2010 10:07

    I got that one wrong, because I noticed VARCHAR2 instead of NUMBER. But of course there is an implicit conversion, so the desired output did happen. My bad ;-)

    But I do believe, that if you do create NVL2 function in the database in order to be able to use it from PL/SQL, the best practice would probably be to create several overloaded versions of the function with each desired datatype. (The body of the overloaded functions would be identical in all cases :-)

    ReplyDelete
  2. iudith01 November, 2010 19:17

    Hello All,

    I think it is not the best idea to create a standalone function in the database with the same name as one of the built-in Oracle functions, even not in PL/SQL only,
    in the hope that Oracle will "turn to better sentiments" towards us and add it in PL/SQL also.

    Somewhere I read that the lastest version of Oracle ( probably 11gR2 ) removes all those annoying "barriers" between SQL and PL/SQL,
    but, unfortunately this is not (yet ?!?)
    the case for NVL2 ... which is still a pure SQL function ...
    Why ?
    I think for everybody this is a mistery, especially for such a trivial function :( :(.

    DECODE was "with us" in exhibiting this behavior from the very first version of PL/SQL,
    and this "gray list" (not to say black...)
    is just getting longer ...

    To Steven and everybody,
    Have a nice month & Best Regards !
    Iudith Mentzel

    ReplyDelete

Subscribe to: Post Comments (Atom)