From the documentation:
SET NULL text
Sets the text displayed whenever a null value occurs in the result of a SQL SELECT command.
Use the NULL clause of the COLUMN command to override the setting of the NULL variable for a given column. The default output for a null is blank ("").
So by default, an empty string("") will be whenever a null value occurs in the result of a select.
However, when print null elements of a varray, "NULL" is displayed for nulls, even set null ""
bash-4.1$ cat foobar.sql
spool foobar.log
set echo on
create or replace type typ1 as varray(2) of binary_float;
/
create table t (x typ1);
insert into t values (typ1(null, NULL));
select * from t;
set NULL ""
select * from t;
drop table t purge;
drop type typ1;
exit;
bash-4.1$ sqlplus -x scott/tiger @foobar
SQL> create or replace type typ1 as varray(2) of binary_float;
2 /
Type created.
SQL> create table t (x typ1);
Table created.
SQL> insert into t values (typ1(null, NULL));
1 row created.
SQL> select * from t;
X
--------------------------------------------------------------------------------
TYP1(NULL, NULL)
SQL> set NULL ""
SQL> select * from t;
X
--------------------------------------------------------------------------------
TYP1(NULL, NULL)
SQL> drop table t purge;
Table dropped.
SQL> drop type typ1;
Type dropped.
SQL> exit;
Thanks for providing a complete test case!
This appears to be a simple bug/oversight. If you set null to any other string, the varrays do take on this value. So you can get the blank behaviour by supplying a space:
SQL> col x format a20
SQL> create table t (x typ1, y int);
Table created.
SQL> insert into t values (typ1(null, NULL), null);
1 row created.
SQL> select * from t;
X Y
-------------------- ----------
TYP1(NULL, NULL)
SQL> set NULL "~"
SQL> select * from t;
X Y
-------------------- ----------
TYP1(~, ~) ~
SQL> set NULL " "
SQL> select * from t;
X Y
-------------------- ----------
TYP1( , )
SQL> set NULL ""
SQL> select * from t;
X Y
-------------------- ----------
TYP1(NULL, NULL)