When migrating from Solaris servers to Linux servers, isql seemed to return more trailing spaces (in data files / columns) on linux servers than in Solaris servers.
old-isql version : Sybase CTISQL Utility/15.0/P-EBF16309 ESD #16/DRV.15.0.7/i386/Solaris 10/BUILD1500-112/OPT/Sun Oct 12 20:37:16 2008
new-isql version : SAP CTISQL Utility/16.0 PL04/P-EBF23393/DRV.16.0.00.04/Linux Intel/Linux 2.6.18-128.el5 i686/BUILD1600-004/OPT/Fri Aug 22 02:47:30 2014
old-isql-data :
|aaaa |bbbb |
new-isql-data :
|aaaa |bbbb |
this makes data-loaders based on positions to fail.
is there a parameter somewhere to control these trailing spaces in data columns.
data file is extracted using isql command of sybase. data-loader is importing data into oracle database using the command sqlldr.
Example of position-based loader :
LOAD DATA
INTO TABLE BPSA_TRANSACTION
TRAILING NULLCOLS
(
INSTR_ID POSITION (1:11) "decode(trim(:INSTR_ID),'NULL',NULL,trim(:INSTR_ID))" ,
BK_ID POSITION (12:17) "decode(trim(:BK_ID),'NULL',NULL,trim(:BK_ID))" ,
ACCT_ID POSITION (18:26) "decode(trim(:ACCT_ID),'NULL',NULL,trim(:ACCT_ID))" ,
...etc
Thanks
Finally I will opte for this solution : Instead of changing all positions in all data-loader control files to match the new field positions due to extra-spaces, I will rather use separator into control file to parse fields.
The separator was ignored (replaced by space) in current sources presumably for performance reason (as the loader does not have to compute these positions for each record ).