Consultor Eletrônico

Unknown Value, Null and Zero length string support in Oracle


There are THREE distinct values for a character string in PROGRESS:
1. The unknown value, ?
2. A zero length string, ""
3. A single space, " "

ORACLE has only TWO distinct values:
1. A NULL column
2. A single space.

If one attempts to insert '' (Zero lenght string) into a column in
ORACLE, the NULL value is placed there, NOT A ZERO LENGTH string.
Try the following demonstration in sqlplus:
sql> create table test (x varchar(10));
sql> insert into test (x) values ('');
sql> select count (*) from test where x = '';
COUNT(*)
----------
0

sql> select * from test;
X
----------

This shows that attempting to set a column in ORACLE to a zero lenght
string actually sets it to NULL. We map the PROGRESS unknown value
to ORACLE's NULL value. The basic problem is:
PROGRESS has three distinct values.
ORACLE has only TWO distinct values.

We map these THREE values into TWO values in ORACLE.
When we retrieve the data (in this case a single space), we have no
way to determing if it came into existance by inserting a space (' ')
or a zero length string ('', which the dataserver will actually
change to a single space, since having it stored as a NULL will cause
many other problems (uniqueness will no longer be enforced, the
column must be nullable, ...).

The dataserver actually stored Zero length string ('') as single space
string (' ').

ICK 09/03/96

Progress Software Technical Support Note # 16082