I am facing the field in data file exceeds maximum length in sql loader error while loading the data to oracle.
//Below control file is used for sqlldr
// Control File: Product_Routing_26664.ctl
//Data File: phxcase1.pr
//Bad File: Product_Routing_26664.bad
LOAD DATA
APPEND INTO TABLE PEGASUSDB_SCHEMA.PRODUCT_ROUTING
FIELDS TERMINATED BY "^"
TRAILING NULLCOLS
(
OID_INST
,SEQ
,ROUTING_TYPE CHAR "(CASE WHEN trim(:ROUTING_TYPE) IS NULL THEN ' ' ELSE trim(:ROUTING_TYPE) END)"
,ODPD_KEY
,PROD_OFFSET
,EFF_DAYS_Z CHAR "(CASE WHEN trim(:EFF_DAYS_Z) IS NULL THEN ' ' ELSE trim(:EFF_DAYS_Z) END)"
,NETWORK_RTG_ID "substr(trim(:NETWORK_RTG_ID), 3, 26)"
,WT0
,WT1
,WT2
,WT3
,WT4
,WT5
,WT6
,WT7
,WT8
,WT9
,WT10
,WT11
,WT12
,WT13
,WT14
,WT15
,WT16
,WT17
,WT18
,WT19
,WT20
,WT21
,WT22
,WT23
,WT24
,WT25
,WT26
,WT27
,WT28
,WT29
,WT30
,WT31
,WT32
,WT33
,WT34
,WT35
,PCS0
,PCS1
,PCS2
,PCS3
,PCS4
,PCS5
,PCS6
,PCS7
,PCS8
,PCS9
,PCS10
,PCS11
,PCS12
,PCS13
,PCS14
,PCS15
,PCS16
,PCS17
,PCS18
,PCS19
,PCS20
,PCS21
,PCS22
,PCS23
,PCS24
,PCS25
,PCS26
,PCS27
,PCS28
,PCS29
,PCS30
,PCS31
,PCS32
,PCS33
,PCS34
,PCS35
,PR_TYPE CHAR "(CASE WHEN trim(:PR_TYPE) IS NULL THEN ' ' ELSE trim(:PR_TYPE) END)"
,PRODUCT_ROUTING_OID "PRODUCT_ROUTING_SQ.nextval"
,COMMON_CASE_OID CONSTANT "1"
,NETWORK_RTG_OID "(select NETWORK_RTG_OID from NETWORK_RTG where NETWORK_RTG_ID = substr(TRIM(:NETWORK_RTG_ID), 3, 26) and COMMON_CASE_OID = 1)"
)
Error: Record 2: Rejected - Error on table PEGASUSDB_SCHEMA.PRODUCT_ROUTING, column OID_INST. Field in data file exceeds maximum length
I have tried changing the OID_INST
column to OID_INST CHAR(4000)
but it shows the same error.
Please help me in resolving this.