null vs empty string in Oracle [duplicate]

Question

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

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

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

The basic table structure in this case would look something like the following.

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

After creating this table, I'm trying to insert the following INSERT commands alternatively.

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 is enforced on the DESCRIPTION column.

In both of the 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 dropped the not null constraint on the DESCRIPTION column then the basic table structure would look like the following

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

and both of the INSERT commands as specified would be successful. They would create two rows one with a null value and another with an empty string '' in the DESCRIPTION column of the TEMP_TABLE.

Now, if I issue the following SELECT command,

SELECT * FROM temp_table WHERE description IS NULL; 

then it fetches both the rows in which one has a null value and the other has an empty string '' in the DESCRIPTION column.

The following SELECT statement however retrieves no rows from the TEMP_TABLE

SELECT * FROM temp_table WHERE description=''; 

It doesn't even retrieve the row which has an empty string in the DESCRIPTION column.

---

Presumably, it appears that Oracle treats a null value and an empty string '' differently here which however doesn't appear to be the case with the INSERT statement in which both a null value and an empty string '' are prevented from being inserted into a column with a not null constraint. Why is it so?

Answer 1

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

On the other hand, SQL Server would let you do what you are trying to achieve.

There are 2 workarounds here:

1. Use another column that states whether the 'description' field is valid or not
2. Use some dummy value for the 'description' field where you want it to store empty string. (i.e. set the field to be 'stackoverflowrocks' assuming your real data will never encounter such a description value)

Both are, of course, stupid workarounds :)

Answer 2

In oracle an empty varchar2 and null are treated the same, and your observations show that.

when you write:

select * from table where a = ''; 

its the same as writing

select * from table where a = null; 

and not a is null

which will never equate to true, so never return a row. same on the insert, a NOT NULL means you cant insert a null or an empty string (which is treated as a null)