Null vs empty string in Oracle

Question

Possible duplicate:
Why does Oracle 9i treat an empty string as NULL?

I have a table in Oracle 10g named TEMP_TABLE with two columns - id and description for demonstration only.

The id column is the primary key generated by a sequence of type NUMBER(35, 0) not null , and the description column is a type of VARCHAR2(4000) not null .

The basic structure of the table in this case will look something like this:

 +--------------+-----------+---------------+ |Name | Null? | Type | +--------------+-----------+---------------+ |ID | NOT NULL | NUMBER(35) | |DESCRIPTION | NOT NULL | VARCHAR2(4000)| +--------------+-----------+---------------+ 

After creating this table, I try to insert the following INSERT commands.

 INSERT INTO temp_table (id, description) VALUES (1, null); ->unsuccessful INSERT INTO temp_table (id, description) VALUES (2, ''); ->unsuccessful 

Both of them are unsuccessful as obvious, because the not null constraint applies in the description column.

In both cases, Oracle complains

 ORA-01400: cannot insert NULL into ("WAGAFASHIONDB"."TEMP_TABLE"."DESCRIPTION") 

An empty string is treated as a NULL value in Oracle.

---

If I omitted the not null constraint in the description column, then the main structure of the table would look like this

 +--------------+-----------+---------------+ |Name | Null? | Type | +--------------+-----------+---------------+ |ID | NOT NULL | NUMBER(35) | |DESCRIPTION | | VARCHAR2(4000)| +--------------+-----------+---------------+ 

and both INSERT commands, as indicated, will succeed. They would create two rows with the NULL parameter and the other with an empty row '' in the description column TEMP_TABLE .

Now, if I issue the following SELECT command,

 SELECT * FROM temp_table WHERE description IS NULL; 

then it selects rows that have a NULL value, and the other has an empty row '' in the description column.

The following SELECT , however, does not extract rows from TEMP_TABLE

 SELECT * FROM temp_table WHERE description=''; 

It does not even retrieve a row with an empty row in the description column.

---

Presumably, it seems that Oracle treats the NULL value and the empty row '' differently here, but it does not seem to be the case with the INSERT , in which a NULL and the empty row '' cannot be inserted into the column with restriction not null . Why is this so?

Answer 1

This is because Oracle internally changes the empty string to NULL. Oracle simply won't let you insert an empty string.

On the other hand, SQL Server will allow you to do what you are trying to achieve.

There are 2 workarounds here:

• Use another column that indicates whether the description field is valid or not
• Use some dummy value for the description field in which you want to save an empty string. (i.e. set the "stackoverflowrocks" field if your real data will never encounter such a description value)

Both of course are silly workarounds :)

Answer 2

In oracle, empty varchar2 and null are treated the same way, and your observations show this.

when you write:

 select * from table where a = ''; 

this is the same as the record

 select * from table where a = null; 

not a is null

which will never be true, so never return a string. same on insert, NOT NULL means you cannot insert a null or empty string (which is treated as zero)