Nulli Secundus

July 8, 2009

>>Script Language and Platform: Oracle
This script looks at some of the issues in handling nulls.

I love idioms and phrases. One of my favorite phrases is - Nulli secundus (Origin - Latin) adjective, meaning - second to none. I would love to say, null is nulli secundus in Oracle.

Why is null so unique? As per RDBMS definition, the value of null is unknown or undefined. Null is not equal to anything. A null is not equal to another null. As such, handling nulls in sql and pl/sql is very tricky.

Let's look at some of the issues in handling nulls.

drop table null_jp;

create table null_jp 
   (empno number, ename varchar2(20),sal number, comm number) 
tablespace users;

truncate table null_jp;

insert into null_jp(empno,ename,sal,comm)  values(101,'JOSE',1000,''); 
   --No value is inserted. An explicit null.
insert into null_jp(empno,ename,sal,comm)  values(102,'JOJO',null,20); 
   --A null is inserted.  An explicit null.
insert into null_jp(empno      ,sal,comm)  values(103,       1100,90); 
   --An implicit null inserted, by omitting value for ename column.
insert into null_jp(      ename,sal,comm)  values(    'JOJO',1200,40); 
   --An implicit null inserted, by omitting value for empno column.

commit;

SQL> select * from null_jp;

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       101 JOSE                       1000
       102 JOJO                                    20
       103                            1100         90
           JOJO                       1200         40

4 rows selected.

SQL> select count(*) from null_jp;

  COUNT(*)
----------
         4

1 row selected.

SQL> select count(empno) from null_jp;

COUNT(EMPNO)
------------
           3

1 row selected.

The table null_jp has four rows but a count on empno column from the table displays only three rows. Oracle could not count null values in the empno column.

By using an nvl function on the empno column, we can derive the expected result.

SQL> select count(nvl(empno,0)) from null_jp;

COUNT(NVL(EMPNO,0))
-------------------
                  4

1 row selected.

select count(ename) from null_jp;


COUNT(ENAME)
------------------
                 3

1 row selected.

The table null_jp has four rows. Again a count on ename column from the table

displays only three rows. Oracle could not count the null value in the empno column.

To count the null value in the ename column, I used the nvl function on the ename column.

select count(nvl(ename,'X')) from null_jp;

COUNT(NVL(ENAME,'X'))
---------------------
                    4

1 row selected.

SQL> select ename from null_jp;

ENAME
--------------------
JOSE
JOJO

JOJO

4 rows selected.

A null is not equal to anything nor equal to another null.

SQL> select ename from null_jp where ename= (nvl(ename,''));

ENAME
--------------------
JOSE
JOJO
JOJO

3 rows selected.


SQL>  select ename from null_jp where (nvl(ename,'')) = (nvl(ename,''));

ENAME
--------------------
JOSE
JOJO
JOJO

3 rows selected.

How to select the records from the table that has null ename value?

SQL>  select ename from null_jp where ename = '';

no rows selected


SQL> select ename from null_jp where ename = null;

no rows selected

Only "IS NULL" and "IS NOT NULL" can be used to check for values that are null or not null.

SQL> select ename from null_jp where ename is null;

ENAME
--------------------


1 row selected.

SQL> select ename from null_jp where ename is not null;

ENAME
--------------------
JOSE
JOJO
JOJO

3 rows selected.

Using this construct, I can equate a null to another null.

ENAME
--------------------
JOSE
JOJO

JOJO

4 rows selected.

Let us find the records from our table, whose sum of sal + comm is greater than or equal to 20.

SQL> select * from null_jp where (sal + comm ) >= 20;

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       103                            1100         90
           JOJO                       1200         40

2 rows selected.

From my observation, all four rows from the null_jp table satisfy the where condition.

Since two rows have null values in the sal and comm columns, these two rows were not computed by Oracle to evaluate in the where condition of the query.

Using the nvl function on sal and comm columns, this can be resolved.

SQL> select * from null_jp  where (nvl(sal,0) + nvl(comm,0) ) >= 20;

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       101 JOSE                       1000
       102 JOJO                                    20
       103                            1100         90
           JOJO                       1200         40

4 rows selected.

These queries find only the rows having null ename values in the table null_jp.

SQL> select * from null_jp  where ename is null;

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       103                            1100         90

1 row selected.

SQL> select * from null_jp  where nvl(ename,'xyz') = 'xyz';

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       103                            1100         90

1 row selected.

Here is a unique query. If no values are supplied for the ename column, the the query returns all records in the table with NON NULL values for the ename column.

If a value is supplied for ename column, the query returns that specific record from null_jp table, which matches the supplied ename value.

SQL> select * from null_jp where ename = nvl(upper('&ename'),ename);
Enter value for ename:
old   1: select * from null_jp where ename = nvl(upper('&ename'),ename)
new   1: select * from null_jp where ename = nvl(upper(''),ename)

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       101 JOSE                       1000
       102 JOJO                                    20
           JOJO                       1200         40

3 rows selected.


SQL> /
Enter value for ename: jose
old   1: select * from null_jp where ename = nvl(upper('&ename'),ename)
new   1: select * from null_jp where ename = nvl(upper('jose'),ename)

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       101 JOSE                       1000

1 row selected.

In either case, Oracle returns records having NON NULL ename values.

How to get all of the rows from the null_jp table using a where condition on ename column, wherein the ename is either null or not null. (I want to get all four rows from my table):

SQL> select * from null_jp where nvl(ename,'null') = decode(nvl(ename,null),null,'null',ename);

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       101 JOSE                       1000
       102 JOJO                                    20
       103                            1100         90
           JOJO                       1200         40

4 rows selected.

The same result can be obtained from the null_jp table with the following queries:

select * from null_jp where ename is null
union
select * from null_jp where ename is not null;


select * from null_jp where (ename is null or ename is not null);

select ename from null_jp where (nvl(ename,'ZZZ')) = (nvl(ename,'ZZZ'));

Some often overlooked errors, while handling nulls in pl/sql programming.

This cursor loop displays all of the ename values with a counter number, incremented after each iteration in the loop.

set serverout on size 100000
declare
v_num number;
begin
for c1 in (select ename from null_jp) loop
v_num:=v_num+1;
dbms_output.put_line(v_num||' '||c1.ename);
end loop;
end;
/

JOSE
JOJO
JOJO

PL/SQL procedure successfully completed.

Here the v_num value is not displayed in the output. Oracle could not increment the v_num value inside the loop, as v_num is null.

This issue can be fixed in either way:

declare
v_num number:=0;
begin
for c1 in (select ename from null_jp) loop
v_num:=v_num+1;
dbms_output.put_line(v_num||' '||c1.ename);
end loop;
end;
/

By initializing the v_num variable at the beginning of the loop.

declare
v_num number;
begin
for c1 in (select ename from null_jp) loop
v_num:=nvl(v_num,0)+1;
dbms_output.put_line(v_num||' '||c1.ename);
end loop;
end;
/

Or by using the nvl function on the v_num variable inside the loop.

Now I can display the output from my pl/sql script as expected.

1 JOSE
2 JOJO
3
4 JOJO

PL/SQL procedure successfully completed.

The following script displays, all similar enames in the output with a message.

For some reason the c1.ename cursor variable values are not displayed in my output.

declare
v_num number:=0;
v_dup varchar2(20);
begin
for c1 in (select ename from null_jp order by ename) loop
v_num:=v_num+1;
dbms_output.put_line(v_num);
if 	( v_dup = c1.ename) then
dbms_output.put_line(v_num||' '||c1.ename||' duplicate ename found');
elsif 	( v_dup <> c1.ename) then
	v_dup:=c1.ename;
dbms_output.put_line(v_num||' '||c1.ename);
end if;
end loop;
end;
/
1
2
3
4

Oracle could not compare the ename values with v_dup cursor variable, which is null.

As such the duplicates of the ename values are not displayed in the cursor loop output.

This issue can be fixed in either way:

declare
v_num number:=0;
v_dup varchar2(20):='x';
begin
for c1 in (select ename from null_jp order by ename) loop
v_num:=v_num+1;
dbms_output.put_line(v_num);
if 	( v_dup = c1.ename) then
dbms_output.put_line(v_num||' '||c1.ename||' duplicate ename found');
elsif 	( v_dup <> c1.ename) then
	v_dup:=c1.ename;
dbms_output.put_line(v_num||' '||c1.ename);
end if;
end loop;
end;
/

By initialized the v_dup variable before the beginning of the loop.

declare
v_num number:=0;
v_dup varchar2(20);
begin
for c1 in (select ename from null_jp order by ename) loop
v_num:=v_num+1;
dbms_output.put_line(v_num);
if 	( nvl(v_dup,'x') = c1.ename) then
dbms_output.put_line(v_num||' '||c1.ename||' duplicate ename found');
elsif 	( nvl(v_dup,'x') <> c1.ename) then
	v_dup:=c1.ename;
dbms_output.put_line(v_num||' '||c1.ename);
end if;
end loop;
end;
/

Or by using a nvl function on the v_dup variable inside the loop, the issue can be fixed.

1
1 JOJO
2
2 JOJO duplicate ename found
3
3 JOSE
4

After initializing the v_dup string in the script, the script displayed the expected results.

***************************SOME COMPLEXITIES IN UNDERSTANDING NULLS**************************

drop table "  ";

create table "  " (empno number, ename varchar2(20),sal number, comm number) 
tablespace users;

SQL> desc "  "
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(20)
 SAL                                                NUMBER
 COMM

Here I created a table without a name, in fact the table's name is two blank spaces enclosed in double codes. Again, the table name is not null.

truncate table "  ";


insert into "  "(empno,ename,sal,comm)  values(101,'JOSE',1000,''); 
					--No value is inserted. An explicit null.
insert into "  "(empno,ename,sal,comm)  values(102,'JOJO',null,20); 
					--A null is inserted.  An explicit null.
insert into "  "(empno      ,sal,comm)  values(103,       1100,90); 
					--An implicit null inserted, by omitting value for ename column.
insert into "  "(      ename,sal,comm)  values(    'JOJO',1200,40); 
					--An implicit null inserted, by omitting value for empno column.
insert into "  "(empno,ename,sal,comm)  values(105,'    ',2000,10); 
					--A string value of 4 blank spaces is inserted, not null.
insert into "  "(empno,ename,sal,comm)  values(106,'NULL',1100,90); 
					--A string value of NULL is inserted, not null.
commit;

SQL> select * from "  ";

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       101 JOSE                       1000
       102 JOJO                                    20
       103                            1100         90
           JOJO                       1200         40
       105                            2000         10
       106 NULL                       1100         90

6 rows selected.



select * from "  "  where ename='NULL';

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       106 NULL                       1100         90

1 row selected.

I select a record from " " table with ename that contains a string value of 'NULL'.

select * from "  "  where ename=NULL;

no rows selected

How can I select the rows from the table, where the ename is null?

SQL> select * from "  "  where ename is NULL;

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       103                            1100         90

1 row selected.

This record from " " table has a null ename value.

The following record from " " table doesn't have a null ename value. Instead, a string value of four blank spaces was inserted for ename value.

select * from "  "  where ename='    ';

     EMPNO ENAME                       SAL       COMM
---------- -------------------- ---------- ----------
       105                            2000         10

1 row selected.

SQL> create table "  " (empno number, ename varchar2(20),sal number, comm number)
tablespace users;  2
create table "  " (empno number, ename varchar2(20),sal number, comm number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Oracle did not allow me to create another table with two blank spaces as the table's name.

SQL> create table " " (empno number, ename varchar2(20),sal number, comm number)
tablespace users;  2

Table created.

I could create a table with one blank space as my table's name.

SQL> desc " "
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER
 ENAME                                              VARCHAR2(20)
 SAL                                                NUMBER
 COMM 						          NUMBER

If you are creating tables with names containing only blank spaces, then you have to remember the number of blank spaces used for your table name, so as to access and manipulate the tables.

For example, I created two tables, one with one blank space and the second with two blank spaces. Let us see what we make out for these tables from the dba_objects view:

SQL> select object_name from dba_objects 
     where object_type='TABLE' and created > sysdate - 10/1440;

OBJECT_NAME
--------------------------------------------------------------------------------



2 rows selected.

Getting the table_names from the data dictionary that had no visible names is not a straight forward case.

Let's find the tables with blank spaces in the database and find how many blank spaces are in each of our tables' names. Oracle treats the blank spaces as names.

SQL> column object_name format a10
SQL>  select object_name, length(object_name) from dba_objects 
      where object_type='TABLE' and created > sysdate - 10/1440;

OBJECT_NAM LENGTH(OBJECT_NAME)
---------- -------------------
                             1
                             2

2 rows selected.

SQL> drop table " ";

Table dropped.

SQL> drop table "  ";

Table dropped.

SQL> column object_name format a30
SQL> select object_name, length(object_name) from dba_objects 
     where object_type='TABLE' and created > sysdate - 10/1440;

OBJECT_NAME                    LENGTH(OBJECT_NAME)
------------------------------ -------------------
BIN$^402jp403sql404^/Q==$0                  30
BIN$^405jp406sql407^/Q==$0                  30

2 rows selected.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL>  select object_name, length(object_name) from dba_objects 
      where object_type='TABLE' and created > sysdate - 10/1440;

no rows selected

From the foregoing, blank spaces are not treated as null in Oracle. Only when no value is specified for a table's column, does Oracle treat it as null.

Reference:

http://dictionary.reverso.net/english-definitions/nulli%20secundus

http://www.psoug.org/reference/null.html

http://www.databasedesign-resource.com/null-values-in-a-database.html

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home

Nulli Secundus

Some often overlooked errors, while handling nulls in pl/sql programming.

Whitepapers and eBooks

Webcasts

Downloads and eKits

Tutorials and Demos