sql loader [message #450811] |
Fri, 09 April 2010 06:28 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I am using the following control file to load data.
load data
infile inputdata.txt
append
into table mine_test
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(NO_LEGAL_ENTITY "trim(:NO_LEGAL_ENTITY)",
CD_PENSION_277 "trim(:CD_PENSION_277)",
NO_PENSION "trim(':NO_PENSION')",
DT_PEN_START
"to_date(trim:DT_PEN_START),'mm/dd/yyyy')",
DT_PEN_END ,
DT_NEW_PEN_END
"to_date(trim(:DT_NEW_PEN_END),'mm/dd/yyyy')",
ST_PENSION "trim(:ST_PENSION)" ,
DT_PROCESS "trim(:DT_PROCESS)" ,
DS_NOTE char(100))
What i see is the trim is not removing the null spaces for the column ST_PENSION. The data is being loaded succefully but with spaces.
Can you please advice if ther is any limit in using trim in sql loader.
I have nearly 600 records to load.
Regards,
Poiters.
|
|
|
|
|
|
Re: sql loader [message #450822 is a reply to message #450811] |
Fri, 09 April 2010 07:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Great Littlefoot..
My datatype in the destination table is char and i changed it to varchar2.....now its working fine.....
What could be the reason for sqlldr not considerting char datatype to remove spaces....
Does this constraint only for char or any other datatypes...
Regards,
Poiners
|
|
|
Re: sql loader [message #450823 is a reply to message #450811] |
Fri, 09 April 2010 07:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Whole point of char is that it is fixed length.
To be fixed length it pads data with spaces to the column length.
You can't trim a char, it's nothing to do with sqlloader.
|
|
|
|