SQL*Loader issue

[vds]

Hi there,

Of late, we are experiencing a strange problem with sql*loader. It is populating some columns with values that are not in the raw file. For example., the raw file contains
XXXXXXXX |99999999|436|||||||||||||||
XXXXXXXX |44444444|311|||||||||||||||
XXXXXXXX |33333333|042|||||||||||||||
XXXXXXXX |55555555|515|||||||||||||||
XXXXXXXX |66666666|585|||||||||||||||

the value gets inserted as
XXXXXXXX, 99999999, 460001
XXXXXXXX, 33333333, 005100
XXXXXXXX, 55555555, 580100
XXXXXXXX, 66666666, 516049

You can see clearly the value 460001, 005100, 580100 and 516049 are not in the raw file.

The columns in the staging table are all varchar2(2000) to provide for free loading and then edit before loading into the production tables.

This problem happens sporadically and when it does, it causes a lot of havoc and production is down for hours.

The production environment id Oracle 9.2.0.4 and the OS is Red Hat Linux. If any of you gurus have come across such an issue would you please throw some light on what's happening and how do we resolve this ?
Thanks
Vds

 

[Turkbear]

HI,
What's in the .CTL file?

 

[vds]

Here's the structure of the ctl file.

OPTIONS (SKIP=0)
LOAD DATA
INFILE "33.txt" BADFILE "33.bad"
APPEND
INTO TABLE upload
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS

(
JOB_NO CONSTANT '999
CUST_ID_OR_ORG_ID CHAR "ltrim(rtrimcust_id_or_org_id))",
BATCH_NO CONSTANT '33',
COLUMN_01 CHAR "LTRIM(RTRIMCOLUMN_01))",
COLUMN_02 CHAR "LTRIM(RTRIMCOLUMN_02))",
COLUMN_03 CHAR "LTRIM(RTRIMCOLUMN_03))",
COLUMN_04 CHAR "LTRIM(RTRIMCOLUMN_04))",
COLUMN_05 CHAR "LTRIM(RTRIMCOLUMN_05))",
COLUMN_06 CHAR "LTRIM(RTRIMCOLUMN_06))",
COLUMN_07 CHAR "LTRIM(RTRIMCOLUMN_07))",
COLUMN_08 CHAR "LTRIM(RTRIMCOLUMN_08))",
COLUMN_09 CHAR "LTRIM(RTRIMCOLUMN_09))",
COLUMN_10 CHAR "LTRIM(RTRIMCOLUMN_10))",
COLUMN_11 CHAR "LTRIM(RTRIM(REPLACECOLUMN_11, chr(13))))",
COLUMN_12 CHAR "ltrim(rtrimcolumn_12))",
COLUMN_13 CHAR "ltrim(rtrimcolumn_13))",
COLUMN_14 CHAR "ltrim(rtrimcolumn_14))",
COLUMN_15 CHAR "ltrim(rtrimcolumn_15))",
COLUMN_16 CHAR "ltrim(rtrimcolumn_16))",
COLUMN_17 CHAR(1000) "ltrim(rtrim(replacecolumn_17,chr(13))))"
COLUMN_18 CHAR "ltrim(rtrimcolumn_18))",
COLUMN_19 CHAR "ltrim(rtrimcolumn_19))",
COLUMN_20 CHAR "ltrim(rtrimcolumn_20))",
COLUMN_21 CHAR "ltrim(rtrimcolumn_21))",
COLUMN_22 CHAR "ltrim(rtrimcolumn_22))",
COLUMN_23 CHAR "ltrim(rtrimcolumn_23))",
COLUMN_24 CHAR "ltrim(rtrimcolumn_24))",
COLUMN_25 CHAR "ltrim(rtrimcolumn_25))",
COLUMN_26 CHAR "ltrim(rtrim(replacecolumn_26, chr(13))))" --
)