Re: SQL*Plus Copy Command Question

In article <43d66e$mj6_at_newsbf02.news.aol.com>, rspencer_at_aol.com says...
>
>I think the main difficulty is that Oracle utiltities view spaces as
>nulls. A single space can be a valid piece of data. Old time COBOL
>[...]
>world, but it seems like this is an inconsistent implementation. In the
>first instance a space is recognized as a valid piece of data. In the
>second instance it isn't. I have noticed the same problem with
>SQL*Loader.
>
>

Hi!
The problem is not that Oracle recognizes a space sometimes as valid data and sometimes not. A space is always recognized as valid data. However Oracle in it's infinite wisdom decided, that an empty string ('') equals to NULL. So whenever your data is trimmed (which seems to be the case in sql*plus copy), Oracle will consider a space as NULL-value and refuse to insert it in a 'not null' column.
Admittedly this behaviour is quite ugly (and I personally consider it to be a bug) but I'm afraid that Oracle doesn't intend to change it in future releases, so you will have to work around it.

( Note also that you can

     update t set f = '' (if f isn't defined to be not null)   but you cannot retrieve the lines by

     select * from t where f = ''
  because '' equals to NULL and NULL doesn't equal to anything   so you will have to use

     select * from t where f is null
)

Hope this helps

-- 
    _/_/_/   _/_/_/ _/    _/  // Reinhard Kuhn             /  It can be      
   _/    _/ _/     _/  _/    //         (kuhn_at_cas-ps.com) /  done quickly,   
  _/_/_/   _/_/_/ _/_/      // CAS GmbH                  /  cheaply or well  
 _/  _/   _/     _/  _/    // Lemberger Strasse 14      /   - pick any two!  
_/   _/  _/_/_/ _/    _/  // 66955 Pirmasens, Germany  /