Search code examples
oracle11gplsqldeveloper

PL/SQL Developer 11.0.0.1762 - how to write simple script line by line in SQL Window and run them for test purpose?

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.

Solution

  • 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.