ORA 1401 while Loading thru SQLLDR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA 1401 while Loading thru SQLLDR

  1. #1
    Join Date
    Apr 2002
    Posts
    41

    ORA 1401 while Loading thru SQLLDR

    I have a data file whivh has data in it as shown below

    VGNAR170~VAI~9593721~SUMMER06~NTB~~CTO~SONY~0~Y~SVCMODMAS_20060612.slr
    VGNAR170~VAI~9593722~SUMMER06~NTB~~CTO~SONY~0~Y~SVCMODMAS_20060612.slr
    VGNFE690~VAI~9593723~SUMMER06~NTB~~CTO~FOXCONN~0~Y~SVCMODMAS_20060612.slr
    VGNSZ240~VAI~9593724~SUMMER06~NTB~~CTO~SONY~0~Y~SVCMODMAS_20060612.slr
    VGNAR170~VAI~9593725~SUMMER06~NTB~~CTO~SONY~0~Y~SVCMODMAS_20060612.slr
    VGNFE690~VAI~9593726~SUMMER06~NTB~~CTO~FOXCONN~0~Y~SVCMODMAS_20060612.slr

    The control file is as shown below

    LOAD DATA
    INFILE '/interfaces2/ittest/interface/in_done/SONYMSTLOAD.dat'
    REPLACE
    INTO TABLE APPS.SLR_SNY_MODEL_MASTER1
    FIELDS TERMINATED BY '~' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    MODEL_NM CHAR,
    MODEL_TYP CHAR,
    PROD_CD CHAR,
    BUILD_NBR CHAR,
    SUB_CAT CHAR,
    PROD_CAT CHAR,
    PROD_TYP CHAR,
    VENDOR_NBR CHAR,
    NO_GO_STD DECIMAL EXTERNAL,
    NO_GO_ACTIVE CHAR,
    CREATION_DATE SYSDATE,
    CREATED_BY CONSTANT 1000,
    UPDATE_DATE SYSDATE,
    UPDATED_BY CONSTANT 1000,
    ACTION_CODE CHAR,
    CREATION_FILE_NAME CHAR
    )

    if the field is blank then consecutive ~ appear in the file.While loading the data the sqlldr gives the error ORA 1401 Value inserted too large for column.Kindly help

  2. #2
    Join Date
    Apr 2002
    Posts
    41
    The column width in the Tables are sufficiently big to hold the data.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool


    Try re-ordering the fields in the control file:
    Code:
    LOAD DATA
        INFILE '/interfaces2/ittest/interface/in_done/SONYMSTLOAD.dat'
        REPLACE INTO TABLE APPS.SLR_SNY_MODEL_MASTER1
        FIELDS TERMINATED BY '~' OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
    (
     MODEL_NM CHAR
    ,MODEL_TYP CHAR
    ,PROD_CD CHAR
    ,BUILD_NBR CHAR
    ,SUB_CAT CHAR
    ,PROD_CAT CHAR
    ,PROD_TYP CHAR
    ,VENDOR_NBR CHAR
    ,NO_GO_STD DECIMAL EXTERNAL
    ,NO_GO_ACTIVE CHAR
    ,ACTION_CODE CHAR
    ,CREATION_FILE_NAME CHAR
    ,CREATION_DATE SYSDATE
    ,CREATED_BY CONSTANT 1000
    ,UPDATE_DATE SYSDATE
    ,UPDATED_BY CONSTANT 1000
    )

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width