Hi all,

When doing either of these operations in 9i:

create table x as (select * from y);

insert into x(a,b,c) (select a,b,c from y);

....does Oracle trim trailing spaces from varchar2 columns by default
and if so, can it be over-ridden? I'm losing some trailing spaces from
my data during an upgrade and I suspect that one of these operations is
causing it but I've not been able to reproduce the problem in tests...

TIA
Tarby

On Nov 6, 10:13 am, "Tarby777" <nick_william... (AT) mentorg (DOT) com> wrote:

On Nov 6, 10:13 am, "Tarby777" <nick_william... (AT) mentorg (DOT) com> wrote:
Oracle does NOT trim trailing spaces.
It strikes me in your examples in both cases you are using an inline
view where you don't need to. The parentheses are redundant. Maybe they
are causing the problem.
Also '9i' is too inaccurate and doesn't tell anything.

--
Sybrand Bakker
Senior Oracle DBA

Thanks Sybrand,

I'll give it a whirl without the parentheses. BTW, it's 9.2.0.8.0.

Regards,
Tarby

Tarby777 wrote:
Here's a test in 10g. Will also test in 9.2.0.7
No timming.
Also, in line view is when the FROM is a select.
The parens here make no difference...

11:32:05 SQL> create table test(col1 varchar2(10));

Table created.

Elapsed: 00:00:00.01
11:32:17 SQL> insert into test values (' ');

1 row created.

Elapsed: 00:00:00.00
11:32:34 SQL> select length(col1),col1 from test;

LENGTH(COL1) COL1
------------ ----------
10

Elapsed: 00:00:00.00
11:32:55 SQL> create table test2 as select * from test;

Table created.

Elapsed: 00:00:00.39
11:33:31 SQL> insert into test2 select * from test;

1 row created.

Elapsed: 00:00:00.00
11:33:46 SQL> select length(col1),col1 from test;

LENGTH(COL1) COL1
------------ ----------
10

Elapsed: 00:00:00.00
11:34:03 SQL> select length(col1),col1 from test2;

LENGTH(COL1) COL1
------------ ----------
10
10

Elapsed: 00:00:00.03
11:34:13 SQL> insert into test2 (select col1 from test);

1 row created.

Elapsed: 00:00:00.00
11:35:22 SQL> select length(col1),col1 from test2;

LENGTH(COL1) COL1
------------ ----------
10
10
10

Elapsed: 00:00:00.01
11:35:27 SQL>