Re: substituting a space for a NULL

In article <36e015e6$0$5680_at_newton>,
  "Arjan van Bentem" <avbentem_at_DONT-YOU-DAREdds.nl> wrote:
> Roy Brokvam wrote
> > IF :new.mycolumn IS NULL THEN
> > :new.mycolumn := ' ';
> > END IF;
>
> Or:
>
> :new.mycolumn := nvl(:new.mycolumn, ' ');
>
> >However, you are violating the idea of a not null constraint.
>
> Very true!
>
> Arjan.
>
>

Thanks for all the replies. I guess I should address the question of violating the idea behind a not null constraint.

The problem I'm having is that I have a primary key based on a few columns. Sometimes, the value of one or more of these columns is a single space, ' ', which is a perfectly valid non null value. However, my loading software seems to be trimming these single spaces and therefore attempts to insert nulls.

Instead of trying to debug the loading software, I'm trying to hack around the problem within Oracle.

The suggested code specifies one column:

:new.mycolumn := nvl(:new.mycolumn, ' ');

Is there a way I can specify to do this for ALL columns in the table? If you think this is becoming a crazy, illogical thing to do, let me know.

Thanks,

Kevon

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Mar 05 1999 - 15:52:24 CST