DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL*Loader error (invalid zoned decimal nibble)

  1. #1
    Join Date
    Jun 2003
    Posts
    22

    SQL*Loader error (invalid zoned decimal nibble)

    I'm running 8.1.6 on RedHat6.2 and have been trying to load a .csv file into an Oracle table.

    The data in the flat-file is (first few rows):
    1,Thyristor module,non-pcb,EUPEC / MONOBLOK,145,EA,662.00,95990.00
    2,Diode module,non-pcb,USHA / SIEMENS,122,EA,430.00,52460.00
    3,Combined module P-425,non-pcb,IR,140,EA,1211.00,169540.00
    4,Combined module PBH308AC,non-pcb,,0,EA,1150.00,0.00

    The table mat_npcb to be loaded is:
    item_no not null number
    descrp varchar2(40)
    part_type varchar2(15)
    make varchar2(25)
    stk_qty number(5)
    unit varchar2(10)
    rate number(7,2)
    stk_amnt number(10,2)

    The SQL*Loader control file is:
    load data
    infile 'mat_aud1.csv'
    into table mat_npcb
    fields terminated by ',' optionally enclosed by '"'
    trailing nullcols
    (item_no integer external,
    descrp,
    part_type,
    make,
    stk_qty integer external,
    unit,
    rate decimal (7,2),
    stk_amnt decimal (10,2))

    The errors in the log file are:
    Record 1: Rejected - Error on table MAT_NPCB, column STK_AMNT
    Invalid zoned decimal nibble.
    Record 2: Rejected - Error on table MAT_NPCB, column STK_AMNT
    Invalid zoned decimal nibble.
    Record 3: Rejected - Error on table MAT_NPCB, column STK_AMNT
    Invalid zoned decimal nibble.
    Record 5: Rejected - Error on table MAT_NPCB, column STK_AMNT
    Invalid zoned decimal nibble.

    ** All the errors messages pertain to stk_amnt and few records (ie: 4 and 8) which are not mentioned in the log or bad files have been omitted by SQL*Loader. No data was loaded into the mat_npcb table, een the records that were omitted from the log and bad files. The records that were omitted had stk_amnt = 0.00 **

    How can I rectify this problem so that a decimal value like 95990.00 can be inserted into the table? Thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    use float external, or double external -- decimal is for packed decimal format
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2003
    Posts
    22
    Thanks slimdave for the help. I tried using "float external" and "double external" in place of "decimal", but neither worked. Just using "float" does work. By the way what is the packed decimal format? Thanks again.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by padawin
    By the way what is the packed decimal format? Thanks again.
    It's some mainframe thing -- google will give you details
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Aug 2001
    Location
    cuddapah
    Posts
    145
    !here akram shaik

    i works perfectly with out any error check out.

    load data
    infile 'e:\new1.txt'
    into table t_date
    fields terminated by ',' optionally enclosed by '"'
    trailing nullcols
    (item_no integer external,
    descrp,
    part_type,
    make,
    stk_qty integer external,
    unit,
    rate CHAR "TO_NUMBER(:RATE,'999999.99')",
    stk_amnt CHAR "TO_NUMBER(:stk_amnt,'999999.99')")


    akram shaik DBA
    akram_tri_a@yahoo.com

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Originally posted by smdakram
    !here akram shaik




    akram shaik DBA
    akram_tri_a@yahoo.com
    why oh why did you re-open a thread which is nearly 18 months old?

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