OraFAQ Forum: Server Utilities » SQLLDR issue whit leading white spaces.

Home » RDBMS Server » Server Utilities » SQLLDR issue whit leading white spaces.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLLDR issue whit leading white spaces. [message #217391]

Thu, 01 February 2007 23:50

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'
)

Report message to a moderator

Re: SQLLDR issue whit leading white spaces. [message #217392 is a reply to message #217391]

Fri, 02 February 2007 00:04

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

Report message to a moderator

Re: SQLLDR issue whit leading white spaces. [message #217462 is a reply to message #217392]

Fri, 02 February 2007 06:14

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Did you try the functions ltrim and rtrim?

Report message to a moderator

Re: SQLLDR issue whit leading white spaces. [message #217466 is a reply to message #217391]

Fri, 02 February 2007 06:22

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!

Report message to a moderator

Re: SQLLDR issue whit leading white spaces. [message #217470 is a reply to message #217466]

Fri, 02 February 2007 06:31

Mahesh Rajendran
Messages: 10707
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

Report message to a moderator

Re: SQLLDR issue whit leading white spaces. [message #217472 is a reply to message #217470]

Fri, 02 February 2007 06:41

Littlefoot
Messages: 21811
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.

Report message to a moderator

Re: SQLLDR issue whit leading white spaces. [message #217473 is a reply to message #217472]

Fri, 02 February 2007 06:53

Mahesh Rajendran
Messages: 10707
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 21:29:40 CDT 2024