Hi Everyone, newbie poster here, so please bare with me.
Environment:
Oracle 7.3 (no, don't laugh, it's true)
SQL*Loader
Task:
To load/reload a single table with about 1600 records on a fairly regular basis
Problem:
I've been loading data successfully for a while now but this latest reload rejects one record saying that a value is too large.
Table definition is:
SEQ NUMBER ,
MULTI_VALUE_SEQ NUMBER ,
TABLE_NAME VARCHAR2(10) ,
COLUMN_NAME VARCHAR2(25) ,
MPOS_SALESID VARCHAR2(50) ,
LOCAL_DATA VARCHAR2(1) ,
DATA_SOURCE VARCHAR2(50) ,
DEFAULT_VALUE VARCHAR2(20) ,
GENERATION_RULE VARCHAR2(25) ,
RULE_DATA VARCHAR2(1) ,
MPOS_DATA_LOCATION VARCHAR2(150) ,
DESCRIPTION VARCHAR2(100) ,
TRANSLATE VARCHAR2(1) ,
VALIDATION_RULE VARCHAR2(25) ,
ISBOOLEAN VARCHAR2(1) ,
ISDATE VARCHAR2(1) ,
VALUE_IN VARCHAR2(2000) ,
MIN_VALUE NUMBER ,
MAX_VALUE NUMBER ,
ISNUM VARCHAR2(1) ,
ISCURRENCY VARCHAR2(1) ,
ISPERCENTAGE VARCHAR2(1) ,
ISVALID VARCHAR2(1) ,
ISRECEIVED VARCHAR2(1) ,
DATA_VALUE VARCHAR2(200) ,
TRANSLATED_DATA_VALUE VARCHAR2(200) ,
ISRECORDSET VARCHAR2(1) ,
RECORDSET_COLUMN VARCHAR2(150) ,
ISATTRIBUTE_HOLDER VARCHAR2(1) ,
ATTRIBUTE_LIST VARCHAR2(200)
abreviated sqlldr control file is (failing record is the last one and I've put a line between each record to make it easier to read):
LOAD DATA
INFILE *
BADFILE 'mpos_mapping.bad'
DISCARDFILE 'mpos_mapping.dsc'
REPLACE
INTO TABLE mpos_mapping
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
SEQ,
MULTI_VALUE_SEQ,
TABLE_NAME,
COLUMN_NAME,
MPOS_SALESID,
LOCAL_DATA,
DATA_SOURCE,
DEFAULT_VALUE,
GENERATION_RULE,
RULE_DATA,
MPOS_DATA_LOCATION,
DESCRIPTION,
TRANSLATE,
VALIDATION_RULE,
ISBOOLEAN,
ISDATE,
VALUE_IN,
MIN_VALUE,
MAX_VALUE,
ISNUM,
ISCURRENCY,
ISPERCENTAGE,
ISVALID,
ISRECEIVED,
DATA_VALUE,
ISRECORDSET,
RECORDSET_COLUMN,
ISATTRIBUTE_HOLDER
)
BEGINDATA
1,1,ACF02,MORTGAGE,,Y,DEFAULT,isMortgage,,N,,Mortgage Reference,N,,N,N,,,,N,N,N,N,N,,N,,N,
1,1,ACF02,CLISTATUS,,Y,DEFAULT,B,,N,,Client Status,N,,N,N,,,,N,N,N,N,N,,N,,N,
1,1,ACF02,F02SEQNUM,,N,MPOS,,f_2-7-1-3,N,Customers.1.EarningsRank,Client Sequence,N,,N,N,,0,4,Y,N,N,N,N,,N,,N,
1,1,MAT15,FEECODE,,N,MPOS,,,N,Product.Set.Fees,Fee Code,Y,,N,N," AF1,AF2,AF3,AF4,AF6,AF7,AF8,AFA,AFB,AFC,AFD,AFE,AFF,AFG,AFH,AFI,AFJ,AFK,AFL,AFM,AFN,AG1,AG2,AG3,AG4,AG5,AG6,AG7,AFP,AFQ,AFR,AFS,AFT,A FU,AFV,AFW,AFX,AFY,AFZ,AR2,AR3,AR4,AR5,AR6,AR7,AR8,FA1,INR,INS,LE1,LE2,MG1,MG3,MIG,R10,R11,R12,R13,R14,R15,R16,R17,R18,R19,R20,R21,R2 2,R23,R25,R26,R27,R28,R29,R30,R31,R32,R33,R34,R35,R36,R37,R38,R39,R40,R41,R42,R3,R4,R5,R6,R7,R8,R9,TT,XRE ",,,N,N,N,N,N,,Y,FeeCode,Y,
The offending value is the long string "AF1,AF2,AF3,AF4,AF6, .... etc. which is destined for the VALUE_IN column.
This is 371 bytes in length, i.e. much less than the 2000 defined for VALUE_IN.
If I cut the string in half (or at least shroter) it loads fine.
I know I must be doing something really stupid but I just can't see it.
Any help will be mush appeciated.
Regards,
Phil.
|
] 
Blog
Search
Help
Members
Register
Login
Home
)