Organizational Research By

Surprising Reserch Topic

null vs empty string in oracle


null vs empty string in oracle  using -'oracle,null,oracle10g,string'


  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?
    
asked Sep 15, 2015 by LiliaV58lsm
0 votes
8 views



Related Hot Questions



Government Jobs Opening


...