Re: SQL*Loader problem with trailing spaces

Mark Schaefer wrote:
>
> I'm trying to use SQL*Loader into a 7.3.2 database. The problem I'm
> having is that I can't get the spaces off the end of a column. I think
> I haven't run into this problem before because our databases were all
> loaded with the same version of SQL_loader as this onw. However, the
> database I'm loading currently was imported from version 6 (which is our
> requirement)
>
> Here's the problem:
> INSERT INTO TABLE TAIRPORT_TEMP
>
> ... apt_id POSITION(1:6) CHAR, ...
>
> SQL > select translate)apt_id,' ','X') from tairport_temp;
>
> transl
> ______
> AAAXXX
> A012XX (, etc....)
>
> next try:
>
> ... apt_id POSITION(1:6) CHAR "RTRIM(:APT_ID)", ...
>
> SQL > select translate)apt_id,' ','X') from tairport_temp;
>
> transl
> ______
> AAAXXX
> A012XX (, etc....)
>
> now the clincher...
>
> SQL> select translate(apt_id,' ','X') from tairport;
>
> transl
> ______
> AAA
> A012 (,etc...)
>
> Three questions:
>
> 1) Why does the SQL*loader ignore my RTRIM command?

Maybe this is because your data type is CHAR and regardless of RTRIM the spaces are preserved. Maybe you can try this with varchar2(6).

How about trying

SQL > insert into tairport values(...,rtrim('AAA'),...);
SQL > insert inro tairport values(...,rtrim('BBB   '),...);
SQL > select translate(apt_id,' ','X') from tairport;

> 2) Is this different from other versions (earlier versions) of SQLLDR?
> 3) What can I do about it?
>
> Thanks for any solutions
> Mark Schaefer
> mschaefer_at_dsai.com
Received on Sun Jul 14 1996 - 00:00:00 CEST