Oracle confuses character string '' and NULL values

We have been having a very tough problem with Oracle. We are using NEXTSTEP's DBKit and the Oracle adaptor to access our Oracle Sun server from NEXTSTEP applications. We have several columns that are CHAR NOT NULL. In our application written in Objective-C, CHAR data is stored as C-strings. Often our C-string data is blank, so it is represented as a zero-length character string. When this data is inserted into a table, the adaptor creates an INSERT statement that supplies a zero-length character string in the host variable. Unfortunately, Oracle barfs as in the following example:

   SQL> create table x (a char(4) not null);    Table created.

   SQL> insert into x values ('a');
   1 row created.

   SQL> insert into x values (' ');
   1 row created.

   SQL> insert into x values ('');
   ERROR at line 1:
   ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert

Now, I understand what is happening. If I take off the "not null" clause, this works, but the column ends up with a NULL value. If I make the column a VARCHAR, it also works as it should, and the column is a zero-length string.

However, this behavior, i.e., implicitly treating a zero-length character string being applied to a CHAR column as a NULL, is not ANSI standard SQL. A zero-length character string when inserted into a fixed-length character column should be, according to the ANSI standard, padded with blanks on the right. ANY CHARACTER STRING IS PADDED WITH BLANKS ON THE RIGHT AND THIS SHOULD BE NO EXCEPTION! THE ZERO-LENGTH CHARACTER STRING IS NOT, REPEAT IS NOT(!!!) EQUIVALENT TO A NULL VALUE, and Oracle's implementation is clearly faulty.

There are only three legal ways to get a NULL value into a table in SQL. The first is to use the keyword NULL to assign a NULL value to a column in a row during INSERT or UPDATE. The second is to leave a nullable column out of an INSERT statement. The third is to add a column to the table. INSERTING A ZERO-LENGTH CHARACTER STRING IS NOT A VALID WAY TO GET A NULL INTO THE TABLE! BTW, this same thing happens on query, that is, using a '' in a WHERE clause seems to be treated the same as IS NULL:

  SQL> select count(*) from x where a = ' '

    COUNT(*)

           1

  SQL> select count(*) from x where a = ''

    COUNT(*)

           0

Again, this is a faulty implementation of ANSI SQL, in which ONLY the use of IS NULL or IS NOT NULL is supported to differentiate NULLs from valued columns. Since Oracle touts its Level 2 ANSI SQL compliance, I would like to know how they passed the certification benchmarks (if indeed they did)?

To make matters even stranger, watch what happens when we remove the NOT NULL in the column:

  SQL> drop table x;
  Table dropped.

  SQL> create table x (a char(4));
  Table created.

  SQL> insert into x values ('a');
  1 row created.

  SQL> insert into x values (' ');
  1 row created.

  SQL> insert into x values ('');
  1 row created.

Notice that the third row is now entered into the table (presumably as a NULL value). Now we do some queries:

  SQL> select count(*) from x where a = ' ';

    COUNT(*)

           1

  SQL> select count(*) from x where a = '';

    COUNT(*)

           0 (!!!!!)

  SQL> select count(*) from x where a is null;

    COUNT(*)

           1   

Although it seems that using '' to represent a NULL value on INSERT works, the value '' can NOT be used to match a value or a NULL value on query!!!

The reason this is such a problem is that it is not compatible with any other SQL database that I am familar with (including DB2, SQL/DS, Sybase, Informix or Teradata). I know that I can hack my code to return a single blank character string instead of a zero-length character string but then my VARCHARs won't be right (or I have to know the difference between the two types of columns, which is not always easy or possible). Anyway, I don't wanna! :-)

Does anyone at Oracle have a comment on this issue? Thanks,

Jon Rosen

-- 
Jon Rosen
"So remember when you're feeling very small and insecure, how amazingly unlikely is your birth, and pray that there's intelligent life somewhere up in space, because there's bugger all down here on Earth." - Monty Python