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.

0

There are 0 best solutions below