Re: blank fields in Oracle

Thanks for the reply. So you're saying that if we use CHAR, Oracle will preserve a blank in the field, even if we do not specify PRESERVE BLANKS in the control file? If so, that's actually the behavior we want. It's okay if Oracle treats a trimmed blank as a null. My problem is that our Database Management folks are specifying that all character fields, except those with a length of 1, have to be varchar2. If I understand what you're saying, we actually need CHAR for all our elements except a few that need greater than 2000 byte length. Um, I need to convince our db person to allow me to test this. That way I might be able to make a case for an exception to the rule.

Thanks,
Ray Porter

"FC" <flavio_at_tin.it> wrote in message
news:Sg0ca.73179$zo2.1898550_at_news2.tin.it...
>
> Did you try using the CHAR datatype?
> If you use CHAR, the minimum length is 1, max is 2000 (at least in version
> 8.1.7), if you insert a zero length string, Oracle will pad it with a
blank
> automatically.
> Oracle treats zero length strings as nulls, you can easily test this
> situation executing the following sql:
>
> SELECT NVL2(TRIM(' '), 'NOT NULL', 'NULL') FROM DUAL;
>
> result: 'NULL'
>
> SELECT NVL2(TRIM('X'), 'NOT NULL', 'NULL') FROM DUAL;
>
> result: 'NOT NULL'
>
>
> Bye,
> Flavio
>
>
Received on Thu Mar 13 2003 - 09:19:58 CST