Re: Null's vs Empty string

Dick van Oordt <dvoordt_at_pi.net> 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.

I'm assuming that you have a lot of old legacy data where the columns are NULL to begin with, but they're not supposed to be, right? Otherwise, why would you want a user to be able to enter a row into a table if all of the expected data is not present?

If your desire is to allow a user to leave a column blank (NULL) then don't place a NOT NULL constraint on that column. If though, you are being forced to convert old data that isn't quite up-to-snuff to begin with, check out the NVL function. This function lets you replace NULLs with values of your choosing. It works something like this:

select

   NVL(hire_date,sysdate)
from

   sometable;

update

   sometable
set

   hire_date = NVL(todays_date,sysdate);

In the first example, you'll never get a NULL hire_date returned because you are substituting the system date for any row where the hire_date is NULL.

In the seond example, you are updating the hire_date column with the value in the todays_date column. If todays_date is NULL, then substitute the system date.

You should be aware that any numeric columns which are left NULL are not considered to be zero in Oracle. You should ALWAYS use the NVL function on numeric columns when doing any type of math. Example:

select

   num_col_1 + num_col_2
from

   sometable;

will not give the expected answer if either column is NULL. You should always write this as:

select

   NVL(num_col_1,0) + NVL(num_col_2,0)
from

   sometable;

to make sure that a zero is added to num_col_1 if num_col_2 is NULL.

Hope this helps.

Herman Means
Oracle DBA Received on Sat Nov 02 1996 - 00:00:00 CET