Back to articles list
- 4 minutes read

Pitfall in Oracle Database: An Empty Text Stored as Null

I like Oracle database. It is efficient, easy to use for beginners and professionals – its tools for query analysis and optimization are masterpieces. But it has a very annoying “feature” – empty text (containing 0 characters) is stored in the database as null.

Where did such a feature come from?

Probably from the ancient ages, just after the dawn of time, i.e., the late 70's [1]. In that age, memory (RAM and disks) was very limited and system designers did their best to use as little memory as possible. In old versions of Oracle DB, the data type VARCHAR (used for storing text data) allowed text values up to 255 characters (in fact – bytes). There was also one additional byte to store text length, so in total there were 256 bytes for one text value, which perfectly fit into memory block structure. DB engine designers probably didn’t want to waste an additional byte for a null/not null flag and decided that a text value with a length of 0 is the same as null.

Why is this wrong?

Null was created to indicate that some data is unknown or some parameter is not selected. In the case of a text field, one of three different cases should apply:

  1. Not empty text is entered (with length greater than zero).
  2. Null is set, which means value is unknown.
  3. Empty text (with zero length), meaning that the attribute’s value is known and it is exactly equal to empty text.

Using Oracle DB you cannot distinguish the second case from the third one.

When does it hurt?

Here is an example of why this limitation causes issues. Imagine we need to store a list of customers for an internet shop. According to the business requirements, you need to enter a phone number and e-mail address to register with the shop. Other data are completed when the customer starts shopping or when an employee calls him/her to complete data. When an invoice is prepared for the customer, data must be complete. The table to store customers is simple:


The problem is with the second given name (also called middle name). In many countries it is optional, so you can give your son the name “John Michael” or “John.” But when you create an official document and put only one given name for a person who has two names, the document is not valid. In the example above you never know whether a null value for the second given name means that the customer has only one given name (data are complete) or if you need to call the customer and ask him for his second given name (data are not complete). You would need an additional flag or special text entered as the second given name which is not a real name (e.g. “no second given name”), but indicates that the customer has only one given name – looks awful, doesn’t it?

After we created a table, we can put some data:

insert into customer (id, first_given_name, second_given_name,
family_name, phone_number, email_address) values
(1, 'John', '', 'Smith', '123456789', 'jsmith@gmail.com');

insert into customer (id, first_given_name, second_given_name,
family_name, phone_number, email_address ) values
(2, 'Michael', 'Peter', 'Brown', '987654321', 'mbrown@gmail.com');

Let’s query the database, I use SQL*Plus:

SQL> select id, first_given_name, second_given_name, family_name
from customer where id = 1;

	ID
----------
FIRST_GIVEN_NAME
-----------------------------------
SECOND_GIVEN_NAME
-----------------------------------
FAMILY_NAME
-----------------------------------
	 1
John

Smith

Looks OK.

SQL> select id, first_given_name, second_given_name, family_name 
from customer where second_given_name = '';

no rows selected

A surprise! Where is John Smith?

SQL> select id, first_given_name, second_given_name, family_name
from customer where second_given_name <> '';

no rows selected

What happened to my data? Is Oracle DB reliable data storage?

SQL> select id, first_given_name, second_given_name, family_name
from customer where second_given_name = 'Peter';

	ID
----------
FIRST_GIVEN_NAME
------------------------------------
SECOND_GIVEN_NAME
------------------------------------
FAMILY_NAME
------------------------------------
	 2
Michael
Peter
Brown

The second record is OK.

select id, first_given_name, second_given_name, family_name
from customer where second_given_name is null;

	ID
----------
FIRST_GIVEN_NAME
-----------------------------
SECOND_GIVEN_NAME
-----------------------------
FAMILY_NAME
-----------------------------
	 1
John

Smith

Finally found him.

What happened? Instead ef empty string database stores null. The problem is that null is special. Comparing anything (including null) with null always gives ”false” logical value. To use null in comparison you need a special operator “is null” or “is not null.”

What can you do about it?

Nothing. Oracle will not change this DB engine behavior even though you may now have plenty of memory. You need to know what the consequences are and learn to live with them. Sometimes, you need an awful workaround. And you may thank Oracle engineers that only text data types are broken and that they didn’t use the same pattern for INTEGER – Oracle does not convert zero to null.


1. Time in Unix systems is stored as number of seconds which elapsed since 00:00:00, Thursday, 1 January 1970. [2]

2. Incidentally, relational databases were invented in 1970 as well.

go to top