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 Go to next message
ashwanth77
Messages: 72
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 Go to previous messageGo to next message
Littlefoot
Messages: 18833
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 #605417 is a reply to message #605416] Thu, 09 January 2014 04:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
Littlefoot wrote on Thu, 09 January 2014 15:24
In your control file, it is 10th column. In your input file, 10th position is a string 'KG'.


14th position it seems. A character type.
Re: ORA-01722: invalid number sql* loader [message #605418 is a reply to message #605417] Thu, 09 January 2014 04:11 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, 14! That's what I counted but (who knows why) wrote 10 instead of 14. Thank you! I've fixed it in my previous message.
Re: ORA-01722: invalid number sql* loader [message #605420 is a reply to message #605417] Thu, 09 January 2014 04:21 Go to previous messageGo to next message
Littlefoot
Messages: 18833
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 Go to previous messageGo to next message
cookiemonster
Messages: 10573
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 #605424 is a reply to message #605420] Thu, 09 January 2014 04:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1091
Registered: May 2013
Location: Hi-tech city
Senior Member
Littlefoot wrote on Thu, 09 January 2014 15:51
ORDER_DATE                           NEXT     *   ,       CHARACTER



Wondering, in OP's log file, date shows as :

INVC_DATE                            NEXT     *  WHT      DATE YYYYMMDD        
Re: ORA-01722: invalid number sql* loader [message #605425 is a reply to message #605424] Thu, 09 January 2014 04:38 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's because the OP specified DATE datatype in control file, here:
INVC_DATE  DATE  'YYYYMMDD',
I did not.
Re: ORA-01722: invalid number sql* loader [message #605428 is a reply to message #605425] Thu, 09 January 2014 04:53 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
@ashwanth77 - I think you're missing a tab.
Re: ORA-01722: invalid number sql* loader [message #605432 is a reply to message #605428] Thu, 09 January 2014 06:37 Go to previous messageGo to next message
ashwanth77
Messages: 72
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 Go to previous messageGo to next message
ashwanth77
Messages: 72
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 Go to previous messageGo to next message
cookiemonster
Messages: 10573
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.
Re: ORA-01722: invalid number sql* loader [message #605436 is a reply to message #605435] Thu, 09 January 2014 07:02 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've only got one tab after KG as well.
Re: ORA-01722: invalid number sql* loader [message #605440 is a reply to message #605436] Thu, 09 January 2014 07:16 Go to previous messageGo to next message
ashwanth77
Messages: 72
Registered: April 2013
Location: India
Member
there is only one tab after ship_from which is tariff ,one after quantity which is price_unit,one after UNIT which is vat_amount and one at the end daily_flag
Re: ORA-01722: invalid number sql* loader [message #605443 is a reply to message #605440] Thu, 09 January 2014 07:34 Go to previous message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
ashwanth77 wrote on Thu, 09 January 2014 13:16
there is only one tab after ship_from which is tariff

Which means that tariff is set to 19968 and quantity is set to KG.
Previous Topic: multi copy of dump file using exp
Next Topic: Bug in sqlldr using trim in ctl (control file)
Goto Forum:
  


Current Time: Sun Apr 20 12:42:28 CDT 2014

Total time taken to generate the page: 0.07614 seconds