Home » RDBMS Server » Server Utilities » ORA-01722: invalid number sql* loader
| ORA-01722: invalid number sql* loader [message #605408] |
Thu, 09 January 2014 03:06  |
 |
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
Please tell me where I am going wrong?
Attached is the log file and snippets of datafile along with the control file !!
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Jan 9 03:54:27 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: C:\Documents and Settings\bg8355\Desktop\invc.ctl
Data File: C:\Documents and Settings\bg8355\Desktop\invc.txt
Bad File: C:\Documents and Settings\bg8355\Desktop\invc.bad
Discard File: C:\Documents and Settings\bg8355\Desktop\invc.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table STAGE_NA_INVOICE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DOC_CATEGORY FIRST * WHT CHARACTER
INVC_NUM NEXT * WHT CHARACTER
INVC_ITEM NEXT * WHT CHARACTER
INVC_DATE NEXT * WHT DATE YYYYMMDD
CUST_COUNTRY NEXT * WHT CHARACTER
CUST_NUM NEXT * WHT CHARACTER
DEL_COUNTRY NEXT * WHT CHARACTER
DEL_PT_ID NEXT * WHT CHARACTER
SHIP_NUM_COS NEXT * WHT CHARACTER
ACCT_NUM NEXT * WHT CHARACTER
PG_ORD_NUM NEXT * WHT CHARACTER
SHIP_FROM NEXT * WHT CHARACTER
TARIFF NEXT * WHT CHARACTER
NULL if TARIFF = BLANKS
QUANTITY NEXT * WHT CHARACTER
PRICE_UNIT NEXT * WHT CHARACTER
NULL if PRICE_UNIT = BLANKS
UNIT NEXT * WHT CHARACTER
VAT_AMT NEXT * WHT CHARACTER
NULL if VAT_AMT = BLANKS
AMT_LESS_VAT NEXT * WHT CHARACTER
TOTAL_INVC_AMT NEXT * WHT CHARACTER
CURRENCY NEXT * WHT CHARACTER
BUS_CAT NEXT * WHT CHARACTER
COUNTRY_CODE NEXT * WHT CHARACTER
CUST_NAME NEXT * WHT CHARACTER
STREET NEXT * WHT CHARACTER
TOWN NEXT * WHT CHARACTER
SHIP_UNIT_CODE_8 NEXT * WHT CHARACTER
HAULIER_NUM NEXT * WHT CHARACTER
CUST_ORDER_NO NEXT * WHT CHARACTER
HAULIER_NAME NEXT * WHT CHARACTER
SHPPING_PT NEXT * WHT CHARACTER
STO_LOC NEXT * WHT CHARACTER
PLANT_DESCR NEXT * WHT CHARACTER
DAILY_FLAG NEXT * WHT CHARACTER
value used for ROWS parameter changed from 64 to 30
Record 1: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 2: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 3: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 4: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 5: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 6: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 7: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 8: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 9: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 10: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 11: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 12: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 13: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 14: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 15: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 16: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 17: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 18: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 19: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 20: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 21: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 22: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 23: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 24: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 25: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 26: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 27: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 28: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 29: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 30: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 31: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 32: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 33: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 34: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 35: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 36: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 37: Rejected - Error on table STAGE_NA_INVOICE, column DEL_PT_ID.
ORA-01722: invalid number
Record 38: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 39: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 40: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 41: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 42: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 43: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 44: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 45: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 46: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 47: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 48: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 49: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 50: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
Record 51: Rejected - Error on table STAGE_NA_INVOICE, column QUANTITY.
ORA-01722: invalid number
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table STAGE_NA_INVOICE:
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 255420 bytes(30 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 60
Total logical records rejected: 51
Total logical records discarded: 0
Run began on Thu Jan 09 03:54:27 2014
Run ended on Thu Jan 09 03:55:35 2014
Elapsed time was: 00:01:08.04
CPU time was: 00:00:00.06
my control file
load data
infile 'C:\Documents and Settings\bg8355\Desktop\invc.txt'
badfile 'C:\Documents and Settings\bg8355\Desktop\invc.bad'
discardfile 'C:\Documents and Settings\bg8355\Desktop\invc.dsc'
append
into table stage_na_invoice
fields terminated by X'9'
trailing nullcols
(
DOC_CATEGORY,
INVC_NUM,
INVC_ITEM,
INVC_DATE DATE 'YYYYMMDD',
CUST_COUNTRY,
CUST_NUM,
DEL_COUNTRY,
DEL_PT_ID,
SHIP_NUM_COS,
ACCT_NUM,
PG_ORD_NUM,
SHIP_FROM,
TARIFF nullif TARIFF=blanks,
QUANTITY,
PRICE_UNIT nullif PRICE_UNIT=blanks ,
UNIT,
VAT_AMT nullif VAT_AMT=blanks ,
AMT_LESS_VAT,
TOTAL_INVC_AMT,
CURRENCY,
BUS_CAT,
COUNTRY_CODE,
CUST_NAME,
STREET,
TOWN,
SHIP_UNIT_CODE_8,
HAULIER_NUM,
CUST_ORDER_NO,
HAULIER_NAME,
SHPPING_PT,
STO_LOC,
PLANT_DESCR,
DAILY_FLAG
)
invc.txt which is my infile
C 1095805900 000010 20131215 US 2002252030 US 2001046251 0087053286 2001046252 2027076286 1724 19968.000 KG 25319.42 25319.42 USD US230101 US KLEEN TEST PRODUCTS 1611 SUNSET RD PORT WASHINGTON 000000000010270375 0015066025 144272-00-05 CUSTOMER PICK UP US07 ZJ CINCINNATI PLANT |
|
|
|
| Re: ORA-01722: invalid number sql* loader [message #605416 is a reply to message #605408] |
Thu, 09 January 2014 03:54  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
What is QUANTITY column's datatype? In your control file, it is 14th column. In your input file, 14th position is a string 'KG'.
"Quantity" sounds as if it is supposed to accept number of kilograms, not kilograms itself.
Does it make any sense?
[EDITED by LF: In order to avoid confusion, changed "10" to "14" after Lalit warned me of the mistake I've made]
[Updated on: Thu, 09 January 2014 04:10] Report message to a moderator |
|
|
|
|
|
|
|
| Re: ORA-01722: invalid number sql* loader [message #605420 is a reply to message #605417] |
Thu, 09 January 2014 04:21 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Lalit Kumar B
14th position it seems. A character type.
If this line:
QUANTITY NEXT * WHT CHARACTER
made you think that it is a character, then you might be right, but you also might be wrong. It doesn't reflect reality, that's why I asked the OP to specify QUANTITY column's datatype.
How can it be wrong? Here you go: I'm creating a table that has a NUMBER datatype column (ORDER_NO):SQL> create table purchase (order_no number, order_date date);
Table created.
Control file:load data
infile *
into table purchase
replace
fields terminated by ','
trailing nullcols
(
order_no,
order_date "to_date(:order_date, 'dd/mm/yyyy hh:mi:ssam')"
)
begindata
100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM
Log file (excerpt); pay attention to ORDER_NO "Datatype" value:
Table PURCHASE, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ORDER_NO FIRST * , CHARACTER --> here! ORDER_NO is NUMBER!!!
ORDER_DATE NEXT * , CHARACTER
SQL string for column : "to_date(:order_date, 'dd/mm/yyyy hh:mi:ssam')"
Table PURCHASE:
7 Rows successfully loaded.
0 Rows not loaded due to data errors. |
|
|
|
| Re: ORA-01722: invalid number sql* loader [message #605421 is a reply to message #605418] |
Thu, 09 January 2014 04:22 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I think the OP expects the blank spaces at the beginning of the 2nd line of data to be treated as a null for tariff.
In which case KG would be 15 and correspond to price_unit.
Looks like that's not happening.
|
|
|
|
|
|
|
|
|
|
| Re: ORA-01722: invalid number sql* loader [message #605432 is a reply to message #605428] |
Thu, 09 January 2014 06:37 |
|
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
DOC_CATEGORY C ,
INVC_NUM 1095805900 ,
INVC_ITEM 000010 ,
INVC_DATE DATE 'YYYYMMDD',20131215
CUST_COUNTRY, US
CUST_NUM 2002252030 ,
DEL_COUNTRY US,
DEL_PT_ID 2001046251,
SHIP_NUM_COS 0087053286 ,
ACCT_NUM 2001046252,
PG_ORD_NUM 2027076286 ,
SHIP_FROM 1724 ,
TARIFF nullif TARIFF=blanks,
QUANTITY 19968.000 ,
PRICE_UNIT nullif PRICE_UNIT=blanks ,
UNIT KG ,
VAT_AMT nullif VAT_AMT=blanks ,
AMT_LESS_VAT 25319.42 ,
TOTAL_INVC_AMT 25319.42,
CURRENCY USD,
BUS_CAT US230101 ,
COUNTRY_CODE US,
CUST_NAME KLEEN TEST PRODUCTS ,
STREET 1611 SUNSET RD,
TOWN PORT WASHINGTON ,
SHIP_UNIT_CODE_8 000000000010270375 ,
HAULIER_NUM 0015066025,
CUST_ORDER_NO 144272-00-05 ,
HAULIER_NAME CUSTOMER PICK UP,
SHPPING_PT US07,
STO_LOC ZJ,
PLANT_DESCR CINCINNATI PLANT,
DAILY_FLAG
these are the values corresponding to the column
the datatype of quantity is number(30),but value is in decimal. eg.19968.000 values after decimal points should be removed 19968 should be inserted
|
|
|
|
| Re: ORA-01722: invalid number sql* loader [message #605433 is a reply to message #605432] |
Thu, 09 January 2014 06:54 |
|
ashwanth77
Messages: 95
Registered: April 2013
Location: India
|
Member |
|
|
here is the structure of the table
CREATE TABLE INVOICE
(
DOC_CATEGORY VARCHAR2(30 BYTE),
INVC_NUM NUMBER(30),
INVC_ITEM NUMBER(11),
INVC_DATE DATE,
CUST_COUNTRY VARCHAR2(2 BYTE),
CUST_NUM NUMBER(10),
DEL_COUNTRY VARCHAR2(2 BYTE),
DEL_PT_ID NUMBER(10),
SHIP_NUM_COS NUMBER(30),
ACCT_NUM NUMBER(30),
PG_ORD_NUM NUMBER(30),
SHIP_FROM VARCHAR2(6 BYTE),
TARIFF NUMBER(30),
QUANTITY NUMBER(30),
PRICE_UNIT NUMBER(8,3),
UNIT VARCHAR2(10 BYTE),
VAT_AMT NUMBER(10,2),
AMT_LESS_VAT NUMBER(10,2),
TOTAL_INVC_AMT NUMBER(10,2),
CURRENCY VARCHAR2(50 BYTE),
BUS_CAT VARCHAR2(30 BYTE),
COUNTRY_CODE VARCHAR2(50 BYTE),
CUST_NAME VARCHAR2(50 BYTE),
STREET VARCHAR2(50 BYTE),
TOWN VARCHAR2(50 BYTE),
SHIP_UNIT_CODE_8 VARCHAR2(50 BYTE),
HAULIER_NUM NUMBER(30),
CUST_ORDER_NO VARCHAR2(30 BYTE),
HAULIER_NAME VARCHAR2(30 BYTE),
SHPPING_PT VARCHAR2(50 BYTE),
STO_LOC VARCHAR2(50 BYTE),
PLANT_DESCR VARCHAR2(50 BYTE),
DAILY_FLAG CHAR(1 BYTE)
) |
|
|
|
| Re: ORA-01722: invalid number sql* loader [message #605435 is a reply to message #605428] |
Thu, 09 January 2014 07:01 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'll say it again:
cookiemonster wrote on Thu, 09 January 2014 10:53@ashwanth77 - I think you're missing a tab.
For the data to line up the way you say there would need to be two tabs after 1724 (ship_from) and two tabs after 19968.000 (quantity). In the data in your original post there is only one tab after each.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 22:18:28 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
