Re: Oracle database configuration options

In article <326fbcb4.23929213_at_news.buffnet.net>, woowoo <woiccak_at_actstesting. com> writes
>I am using many char(##) fields in my tables. When data is entered,
>oracle pads the fields with spaces. For example I have a char(20)
>called name. I enter Tom into my powerbuilder data entry form. When
>I look at the data in oracle, Tom with 17 spaces is stored.
>
>My question: Is there any oracle configuration setting to suppress
>this behavior?
>
>BTW, I know that powerbuilder did not pad the data. It's Oracle...
>
>thanks,
>tom

Oracle7 provides 2 character datatypes - CHAR is fixed length and VARCHAR2 is variable length. This is the behaviour required by the ANSI SQL standard. (there is also VARCHAR which is identical to VARCHAR2, but which Oracle recommends should not be used.

There are subtle differences in how sorting and comparison works as well so some care is needed when converting.

To change from CHAR to VARCHAR ...

For every CHAR column

alter table <tabname> modify <colname> varchar2(xx); update <tabname> set <colname>=rtrim(<colname>);

Depending on the size of your tables, this could take a long time and a lot of rollback segment.

A shorter (but not recommended) solution is to add

compatibility=v6 to init.ora.

This reverts to Version 6 behaviour where CHAR was variable length. If you do this there may be other side effects and you will restrict your ability to use some V7 features.

-- 
Jim Smith