OracleBlog

In Oracle, there is a difference between 'null' and nothing at all. Here is my story.

I discovered this when playing with default values. First, I created a table that had a default value for one column. Then I tried to insert a row that had nothing at all, and it wouldn't use the default value. So I tried inserting a row with null, and it didn't assign the default value. Observe.

SQL> create table atable (
2 last_name varchar2(32) not null,
3 first_name varchar2(32) not null,
4 rating smallint default 0);

Table created.

SQL> insert into atable values ('Smith', 'John', null);

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John

Notice it inserted NULL, and not the default value.

SQL> insert into atable values ('Smith', 'John');
insert into atable values ('Smith', 'John')
*
ERROR at line 1:
ORA-00947: not enough values

Why doesn't this work? You may know this already, but in the words of Dan Morgan: "By not specifying column names you are signifying that you are providing values for ALL columns. This is why it is a very bad practice as doing an ALTER TABLE ADD immediately invalidates all SQL statements."

So let's do it the proper way and see if there is a difference.

SQL> insert into atable (last_name, first_name) values ('Smith', 'John');

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John
Smith John 0

And there is a difference. Excellent. Out of curiousity between the two ways of inserting rows, I tried this:

SQL> insert into atable (last_name, first_name, rating) values ('Smith', 'John', null);

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John
Smith John 0
Smith John

So I was thinking, why isn't this null being replaced with the default value? Isn't the default value there for instances to replace null? If you're as skeptical as I am, it will make sense after this second test, where I add the "NOT NULL" constraint.

drop table atable;

create table atable (
last_name varchar2(32) not null,
first_name varchar2(32) not null,
rating smallint default 0 NOT NULL);

SQL> insert into atable (last_name, first_name, rating) values ('Smith', 'John', null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ROBERT"."ATABLE"."RATING")

That's when I figured out where I was confused. There are 3 things you can assign to a column when doing an insert:
1. a value (eg: 0)
2. null
3. nothing at all

See, there is a difference between #2 (null) and #3 (nothing at all). To wit:
1. Using null (#2) is 'ok' if you are inserting without specifying columns. Nothing at all (#3) is not.
2. Null (#2) will not be replaced by a default value when inserting, whereas nothing at all (#3) will.

When I started looking at stored procedures, I found even more difference between NULL and nothing. My second story starts off as an investigation of passing NULL to stored procedures.

There is no such thing as "NOT NULL" for stored procedure parameters.

I noticed that the "NOT NULL" keyword can't be used in procedure parameters (nor can NULL). Apparently that is for creating tables only. You can not force input parameters to a procedure to be non-null. All you can do is start your procedure by verifying the input parameters.

SQL> create or replace procedure MyProc1 (some_value IN number NOT NULL)
2 AS
3 BEGIN
4 NULL;
5 END;
6 /

Warning: Procedure created with compilation errors.

Nor can you allow it to be NULL.

SQL> create or replace procedure MyProc2 (some_value IN number NULL)
2 AS
3 BEGIN
4 NULL;
5 END;
6 /

Warning: Procedure created with compilation errors.

You can, however, assign default values. However, note the difference between assigning "NULL" and assigning nothing at all.

SQL> create or replace procedure MyProc3 (some_value IN number := 0)
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(some_value);
5 NULL;
6 END;
7 /

Procedure created.

SQL> exec MyProc3();
0

PL/SQL procedure successfully completed.

SQL> exec MyProc3(NULL);

PL/SQL procedure successfully completed.

SQL> exec MyProc3(1);
1

PL/SQL procedure successfully completed.

Bottom line, there is a difference NULL and nothing at all for both tables and stored procedures. So what is a guy to do?
1. Understand there is a difference between NULL and nothing
2. For tables, use "NOT NULL" and for stored procedures, verify the input manually
3. Use default values for both tables and stored procedures when passing nothing at all.