>>Script Language and Platform: Oracle
This script looks at some of the issues in handling nulls.
Author: JP Vijaykumar
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
