How to insert a black into a non-NULL column?

I'd like to define a table such that for all columns when you try to insert a
NULL into a NON NULL column, instead of producing an error, it instead
inserts a blank (' ').

My reason for doing this is that I am using some software loading data into
Oracle that I believe is trimming spaces from strings such that ' ' becomes ''
which is interpreted by Oracle as a NULL value.

Ans1:
You can get what you want by using the default option :

col1 varchar2 not null default ' '

all subsequent inserts will put a spce in the column if the statement does not reference the column.

Ans2:
You can do it in a "before insert or update" trigger:

CREATE OR REPLACE TRIGGER mytable_biur
BEFORE INSERT OR UPDATE
FOR EACH ROW
BEGIN
    IF :new.mycolumn IS NULL THEN
        :new.mycolumn := ' ';                                      (Or:    :new.mycolumn := nvl(:new.mycolumn, ' ');)
    END IF;
END;

However, you are violating the idea of a not null constraint. As the
analyst/designer of your data model, you have decided to make this column
not null for some reason (hopefully), but what you want to do is really
against your own decision!

Hosted by www.Geocities.ws