Thursday, July 9, 2009

NVL, NVL2 and CASE

This blog is simply a note to myself as I attempted to use the NVL2 function in PL/SQL recently. Enjoy.

Oracle's NVL is a great little function that you can use in SQL or PL/SQL to determine if an item is NULL.

SQL eg.
SELECT e.ename, NVL(m.ename, 'I AM DA BOSS!') manager
FROM emp e, emp m
WHERE e.mgr = m.empno (+)
PL/SQL eg
DECLARE
l_empty VARCHAR2(10);
BEGIN
dbms_output.put_line(NVL(l_empty, 'Forgot to init l_empty!'));
END;
And now (well, for a while, actually) Oracle have provided as with a new NVL2 function that allows us to translate a NULL value to another value completely.
SQL eg.
SELECT e.ename, NVL2(e.mgr, 'i am a peon', 'I AM DA BOSS!') who_am_i
FROM emp e;
However, the NVL2 function is not available in PL/SQL! Bah, how annoying! Try it and you will see:
DECLARE
l_empty VARCHAR2(10);
BEGIN
dbms_output.put_line(NVL2(l_empty, 'Ahh, all safe..','Forgot to init l_empty!'));
END;
... gives an error of:
ERROR at line 4:
ORA-06550: line 4, column 25:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
So, what can we do? Why, we can use the CASE statement in all instances!

SQL eg.
SELECT e.ename, CASE
WHEN m.ename IS NULL THEN 'I AM DA BOSS!'
WHEN m.ename IS NOT NULL THEN 'i am a peon'
END manager
FROM emp e, emp m
WHERE e.mgr = m.empno (+)
PL/SQL eg.
DECLARE
l_empty VARCHAR2(10);
BEGIN
dbms_output.put_line(CASE
WHEN l_empty IS NOT NULL THEN 'Ahh, all safe..'
WHEN l_empty IS NULL THEN 'Forgot to init l_empty!'
END);
END;
There is a lot more to type, but it may just be a little more readable.


2 comments:

oraclenerd said...

It gets easier and easier. ;)

Marc Thompson said...

Indeed it does!