Home » RDBMS Server » Server Utilities » SQLLDR issue whit leading white spaces.
SQLLDR issue whit leading white spaces. [message #217391] Thu, 01 February 2007 23:50 Go to next message
puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India
Member

Hi

I have column who has some leading and traling white spaces
While using SQLLDR the loader trim of thoes spaces.

Please suggests how can i save my characters

E.g

My col have values like ' ABC '
While sqlldr put value in coloumn as ABC

Ctl file

LOAD DATA
CHARACTERSET CSET
APPEND
INTO TABLE NCR_CFS_SN_SWAP
WHEN country_code != ' ' and new_serial_number != ' '
Fields Terminated By "|"
TRAILING NULLCOLS
(
COUNTRY_CODE
,OLD_SERIAL_NUMBER
,INSTANCE_ID
,PRODUCT_ID
,NEW_SERIAL_NUMBER
,PROCESSED_FLAG CONSTANT 'R'
,RESULT_FLAG CONSTANT 'N'
,LAST_UPDATE_LOGIN
,LAST_UPDATE_BY CONSTANT '-1'
,LAST_UPDATE_DATE SYSDATE
,USER_NAME CONSTANT SN_USER_NAME
,JOB_FILENAME CONSTANT SN_FILENAME
,CREATION_DATE SYSDATE
,CREATED_BY CONSTANT '-1'
)
Re: SQLLDR issue whit leading white spaces. [message #217392 is a reply to message #217391] Fri, 02 February 2007 00:04 Go to previous messageGo to next message
puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India
Member

Also I would like to add, the values may differ like
' ABC ' , ' WW '

I want to save exact values...

Thanks
Puneet
Re: SQLLDR issue whit leading white spaces. [message #217462 is a reply to message #217392] Fri, 02 February 2007 06:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you try the functions ltrim and rtrim?
Re: SQLLDR issue whit leading white spaces. [message #217466 is a reply to message #217391] Fri, 02 February 2007 06:22 Go to previous messageGo to next message
puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India
Member

ltrim and rtrim might fail as i don't know how many white space will be there...
Could be 7 could be 9 before and after the character.

Thanks anyways!
Re: SQLLDR issue whit leading white spaces. [message #217470 is a reply to message #217466] Fri, 02 February 2007 06:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you atleast bothered to try?
C:\ora>type data.txt
   ABC
        XYZ
DEF
  fghi
C:\ora>type data.ctl
load data
infile 'data.txt'
truncate into table mytable fields terminated by ',' TRAILING NULLCOLS
(
c1 "rtrim(ltrim(:c1))"

)
C:\ora>sqlldr userid=scott/tiger control=data.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Feb 2 07:29:17 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 3
Commit point reached - logical record count 4

C:\ora>sqlplus -s scott/tiger
select * from mytable;

C1
--------------------
ABC
XYZ
DEF
fghi

Re: SQLLDR issue whit leading white spaces. [message #217472 is a reply to message #217470] Fri, 02 February 2007 06:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But isn't the question just the opposite? As I've understood it, @OP's file is like the one you used for test example ('data.txt') but his problem is that SQL*Loader trims data when loading into the table. It appears that trimming has been done, but he'd like to avoid it.

He gets
ABC
XYZ
DEF
but would like to keep
   ABC
        XYZ
DEF

Did I misunderstood the question?

P.S. If it was about storing character values (' 123', '456 ', etc.) into a NUMBER datatype column, I guess that altering a table might solve the problem. But, seeing that alphanumerics are being used, I wouldn't know what went wrong.
Re: SQLLDR issue whit leading white spaces. [message #217473 is a reply to message #217472] Fri, 02 February 2007 06:53 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Yes. I misread. Many thanks for littlefoot.
If the dataype is CHAR/VARCHAR2, the blanks will be preserved as is.
Cannot reproduce the case.
Works as intended for CHAR/VARCHAR2.

[Updated on: Fri, 02 February 2007 06:56]

Report message to a moderator

Previous Topic: DBV-00100 using dbverf80
Next Topic: SQLLDR, dates and null columns
Goto Forum:
  


Current Time: Sat Dec 10 07:21:55 CST 2016

Total time taken to generate the page: 0.13590 seconds