Re: Null's vs Empty string

Dick van Oordt wrote:
>
> I'm just starting to use Oracle, so this question is just
> because of lack of knowledge and experience.
>
> We are converting/testing an application, written in CA/OpenRoad, to
> run against Oracle. Originally it's using OpenIngres.
>
> The OpenIngres database has many varchar columns defined with the
> 'NOT NULL' option. If the user leaves entry fields empty a SQL
> Insert (or Update) statement will be generated:
> INSERT INTO table (key, char_column)
> VALUES (1, '');
>
> This works fine with OpenIngres. However, Oracle seems to convert
> the empty string to a NULL value, resulting in an error on the
> Insert/Update.
>
> I always assumed null's have been introduced to make the distinction
> between 'unknown' and 'empty' (or zero), so I'm wondering if this is
> possible within Oracle ?
> Can I influence this '' to null conversion with some setting ?
>
> Thanks in advance.

Somewhat unfortunate, but Oracle does indeed equate empty strings to nulls. Some RDBMSs actualy have a null indicator against the field to indicate that the field is null vs empty. As of this time, Oracle does not.

Depending on your point of view, this may or may not be a bad thing. One thing it does is force you to discriminat between nulls and defaults.

The best workaround I can suggest is to define a default against these columns.

/Hans Received on Sun Oct 27 1996 - 00:00:00 CEST