Re: substituting a space for a NULL

kevon_at_my-dejanews.com wrote in message <7bosue$pdo$1_at_nnrp1.dejanews.com>...
>I'm a SQL novice, so forgive me if the answer to this is obvious.
>
>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.
>
>Thanks in advance,
>
>Kevon
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

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 := ' ';
    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!

Maybe I understand if you tell me more.

Regards,

Roy Brokvam
roy.brokvam_at_conax.com Received on Fri Mar 05 1999 - 11:08:13 CST