-
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.
-
use float external, or double external -- decimal is for packed decimal format
-
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.
-
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
-
!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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|