0

T-SQL : 

declare @var1 varchar(30), @var2 varchar(30);

select @var1 = null, @var2='';

select isnull(@var1, 'aa') as var1, isnull(@var2, 'aa') as var2;

PL/SQL : (the following does not work although it looks like it should work)

DECLARE var1 varchar(30):= '', var2 varchar(30):= null;
select nvl(var1, 'A value') var1, nvl(var2, 'A value') as var2 from dual;

First it complained about the comma, then it complained about end of file.
So I kept fumbling and tumbling until I found this answer which I totally do not expect.

See my answer to end your frustration.

enter image description here

Above is the version I have. Try to write something like this (like in T-SQL) to test their NVL function but I kept getting unexpected error dialog from Developer's IDE.

Jenna Leaf
  • 2,255
  • 21
  • 29

1 Answers1

0

The answer is RIGHT HERE. But I commented out "SET SERVEROUTPUT ON" because in my environment it doesn't work. After all, the following is working. You can copy and paste that onto your SQL window of PLSQL Developer and modify them anyway you want. Hope my frustration becomes your help.

--set serveroutput on;
DECLARE var1 varchar(30):= ''; var2 varchar(30):= null;
BEGIN
   dbms_output.put_line(nvl(var1, 'A value'));
   dbms_output.put_line(nvl(var2, 'A value'));
END;

I never expect it would take so many lines (instead of 2) if you need to declare variables. Or you can just do this, 

select NVL('', 'something') s1, NVL(null, 'something') s2 from DUAL

p.s. By the way, Oracle's NVL( , ) is better than T-SQL's IsNull( , ) because it also takes care of the empty '' situation.

Jenna Leaf
  • 2,255
  • 21
  • 29
  • 1
    Note that in Oracle, the empty string is NULL which is why NVL behaves differently from IsNull. You can't have a non-NULL empty string in Oracle. Technically, that violates the SQL standard but that's because the Oracle behavior pre-dates the standard https://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null/203536#203536 – Justin Cave Oct 14 '19 at 21:01
  • Justin Cave: Oh, that's right. Thankyou! They did store it as NULL. – Jenna Leaf Oct 14 '19 at 21:03