Array DML and Empty Strings

[BobGeezer]

We're loading table using create table and then
array DML of 250 rows at a go.

We get
"ORA: 01400 Caonnot insert NULL value into (user,table,column)"
... where column is a non-NULL nvarchar2(20) column.

When I step through the code, there are no NULL
values going into the array. Only _blank_
strings sometimes. These blank strings are
sometimes going into the non-NULL columns that
are also in the primary key (index constraint)
and that's when the error is reported during the
load.

How do I ensure these blanks are inserted
correctly? At present I'm substituting the
blanks with a single space - and that makes it
all work. However, our data is compromised and
the application will now not work. This seems to
show the blanks are the problem. Is this because
they're in the primary key? Is array DML
inserting NULLs instead of blanks for me?

Please help; I'm stuck.

 

[Marco Kalter]

In Oracle there is no difference between an empty string and a NULL value, so you cannot insert such a value into a mandatory column. Array DML or otherwise.

 

[BobGeezer]

Hi Marco,

Thanks, but that means Oracle is not a relational
database!!!!! One of the fundamental rules of
relational databases is the NULL is a distinct
value separate completely from all data values.

Have you any idea when Oracle plan to fix this bug?

How can people work with this limitation? It's a
bit of a disaster! What can we do? Do we have
to translate all blank values into a value
of '' and back again in all our code?