null vs empty string in Oracle
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?
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:
- Use another column that states whether the 'description' field is valid or not
- 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 :)
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)
참고URL : https://stackoverflow.com/questions/13278773/null-vs-empty-string-in-oracle
'IT TIP' 카테고리의 다른 글
How to make git log not prompt to continue? (0) | 2020.10.21 |
---|---|
Smooth scroll without the use of jQuery (0) | 2020.10.21 |
Return from lambda forEach() in java (0) | 2020.10.21 |
Should I derive custom exceptions from Exception or ApplicationException in .NET? (0) | 2020.10.21 |
What is COM (Component Object Model) in a nutshell? (0) | 2020.10.21 |