Home » RDBMS Server » Server Utilities » sql loader (oracle 10.2.0.3)
icon13.gif  sql loader [message #450811] Fri, 09 April 2010 06:28 Go to next message
pointers
Messages: 443
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 #450812 is a reply to message #450811] Fri, 09 April 2010 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67846
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is "null space"?

Regards
Michel
Re: sql loader [message #450813 is a reply to message #450811] Fri, 09 April 2010 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67846
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition your control file is not valid.

Regards
Michel
Re: sql loader [message #450816 is a reply to message #450811] Fri, 09 April 2010 06:56 Go to previous messageGo to next message
Littlefoot
Messages: 21670
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
TRIM works just fine on my Oracle 10g.

What is ST_PENSION column's datatype? Is it a CHAR, perhaps? If so, TRIM won't help - you'll need to change its datatype to VARCHAR2.

Anyway, provide some more information. Table description as well as sample input data would be nice.
Re: sql loader [message #450822 is a reply to message #450811] Fri, 09 April 2010 07:14 Go to previous messageGo to next message
pointers
Messages: 443
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 messageGo to next message
cookiemonster
Messages: 13894
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.
Re: sql loader [message #450826 is a reply to message #450811] Fri, 09 April 2010 07:28 Go to previous message
pointers
Messages: 443
Registered: May 2008
Senior Member
Great Thanks for your time....
Previous Topic: How to load if the data is in the control file as well as in the datafile (2 threads merged by bb)
Next Topic: sql loader - how to ignore inserting duplicate records
Goto Forum:
  


Current Time: Tue May 11 06:17:09 CDT 2021