Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Import - Convert char to varchar2 datatype?

Re: Import - Convert char to varchar2 datatype?

From: OraSaurus <granaman_at_not_home.com>
Date: 1998/10/26
Message-ID: <pJRY1.175$B54.6628881@news.rdc1.ne.home.com>#1/1

In article <70lgg4$fi5$1_at_flood.xnet.com>, jjy_at_xnet.com wrote:
>I am importing a set of tables (via export/import) on Oracle 8.
>Most tables have character fields defined using the char(n) datatype
>instead of varchar2(n). In this case, there is no reason to use char over
>varchar2.
>
>I have redefined char(n) fields to be varchar2(n). Is there any way I
>can "squeeze out" the trailing white spaces on existing data? I tried
>exporting and then reimporting after I alter the tables to use varchar2(n),
>but the trailing blanks are not removed on existing data.
>
>Is there any way to automate this conversion process? Manually selecting
>the data and removing trailing white space is not an option. There are
>too many tables and too many columns.

Automate? Sort of... But its ugly!

  1. figure out which columns have the offending trailing white space
  2. write a script to "rtrim" them

For example...

alter table FOO set BAR1 = rtim(BAR1), BAR2=rtim(BAR2), BAR3=rtrim(BAR3);

The usual caveats about mass updates apply - indexed columns (drop the indexes?), rollback segments, frequent commits, etc.

I'm not up on Oracle8 yet, but I don't remember seeing anything new on this.

-OraSaurus Received on Mon Oct 26 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US