Subject Re: RES: [firebird-support] String Concatenation with null field
Author Roger Crämer

--- In, "Martijn Tonies"
<m.tonies@u...> wrote:
> Hi,
> > I agree with you, but I've a lot of clients using my software and the
> > NULL string field was not important when the software was developed.
> > I'll have a lot of work and perhaps problems with validations if I
> > my database defining all string fields as NOT NULL and the default
> > as a empty string.
> >
> > If I alter the database I'll need to import the actual database to
a new
> > and do the validations of null fields.... It'll give me a lot of work!
> >
> > I stay surprise with this because it doesn't occour in Oracle
> That's because Oracle goes against all standards and treats NULL
> as '' (empty string) for character columns.

.. or to make it more descriptive (in Oracle's sqlplus):
SQL> create table test( x varchar(x) not null, y varchar(3));
Table created.
SQL> commit;
Commit complete.
SQL> insert into test ( x, y ) values ( '', '' );

results in:
insert into test( x, y ) values ( '', '' )
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."TEST"."X")

That's not any standard but also no "Bug" in ORACLE - they mention
that behaviour within their handbooks.

So treat Oracle not 'standard conform' - on some points they have
their own standard.


> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle &
> Server
> Upscene Productions
> Database development questions? Check the forum!