Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL*Loader - Rejecting records, invalid number

Status
Not open for further replies.
johnnybee

johnnybee

Programmer
Dec 16, 2002
24
GB
I am trying to load some data into the following table:

Code: 
CREATE TABLE IMP_ECUST
(Source VARCHAR2(1),
Sales_Level_2 VARCHAR2(255),
Sales_Level_3 VARCHAR2(255),
Sales_Level_4 VARCHAR2(255),
Sales_Level_5 VARCHAR2(255),
Sales_Level_6 VARCHAR2(255),
CSC_Global_ID INTEGER,
CSC_Global_Name VARCHAR2(255),
CSC_Company_ID NUMBER,
CSC_Company_Name VARCHAR2(255),
CSC_Site_ID INTEGER,
CSC_Site_Name VARCHAR2(255),
Booking_Fiscal_Quarter VARCHAR2(10),
Sales_Agent_ID INTEGER,
Sales_Agent_Name VARCHAR2(255),
DB_Duns_Number INTEGER,
DB_Tel_Number VARCHAR2(255),
DB_Site_Num_Employees INTEGER,
DB_Company_Num_Employees INTEGER,
DB_Fax_Number VARCHAR2(255),
DB_SIC_Line_Of_Business VARCHAR2(255),
DB_Company_Sic_Code VARCHAR2(255),
DB_Company_Sic_Description VARCHAR2(75),
EMEA_Vertical_Market VARCHAR2(255),
EMEA_Vertical_Market_Sub_Group VARCHAR2(255),
POS_Transaction_ID INTEGER,
POS_Net_Price FLOAT)

For this I am using SQL*Loader, the CTL file is defined as:

Code: 
load data
          infile 'c:\oracle\ora81\bin\imp_ecust.txt'
                  append
		  into table imp_ecust
          fields terminated by "|"
          trailing nullcols		  
(Source,
Sales_Level_2,
Sales_Level_3,
Sales_Level_4,
Sales_Level_5,
Sales_Level_6,
CSC_Global_ID,
CSC_Global_Name,
CSC_Company_ID,
CSC_Company_Name,
CSC_Site_ID ,
CSC_Site_Name,
Booking_Fiscal_Quarter,
Sales_Agent_ID,
Sales_Agent_Name,
DB_Duns_Number,
DB_Tel_Number,
DB_Site_Num_Employees,
DB_Company_Num_Employees,
DB_Fax_Number,
DB_SIC_Line_Of_Business,
DB_Company_Sic_Code,
DB_Company_Sic_Description,
EMEA_Vertical_Market,
EMEA_Vertical_Market_Sub_Group,
POS_Transaction_ID,
POS_Net_Price)

It is rejecting all the records with the following in the log file:

Code: 
SQL*Loader: Release 8.1.7.0.0 - Production on Wed Aug 4 10:09:11 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Control File:   imp_ecust.ctl
Data File:      c:\oracle\ora81\bin\imp_ecust.txt
  Bad File:     imp_ecust.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 100000
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table IMP_ECUST, 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
------------------------------ ---------- ----- ---- ---- ---------------------
SOURCE                              FIRST     *   |       CHARACTER            
SALES_LEVEL_2                        NEXT     *   |       CHARACTER            
SALES_LEVEL_3                        NEXT     *   |       CHARACTER            
SALES_LEVEL_4                        NEXT     *   |       CHARACTER            
SALES_LEVEL_5                        NEXT     *   |       CHARACTER            
SALES_LEVEL_6                        NEXT     *   |       CHARACTER            
CSC_GLOBAL_ID                        NEXT     *   |       CHARACTER            
CSC_GLOBAL_NAME                      NEXT     *   |       CHARACTER            
CSC_COMPANY_ID                       NEXT     *   |       CHARACTER            
CSC_COMPANY_NAME                     NEXT     *   |       CHARACTER            
CSC_SITE_ID                          NEXT     *   |       CHARACTER            
CSC_SITE_NAME                        NEXT     *   |       CHARACTER            
BOOKING_FISCAL_QUARTER               NEXT     *   |       CHARACTER            
SALES_AGENT_ID                       NEXT     *   |       CHARACTER            
SALES_AGENT_NAME                     NEXT     *   |       CHARACTER            
DB_DUNS_NUMBER                       NEXT     *   |       CHARACTER            
DB_TEL_NUMBER                        NEXT     *   |       CHARACTER            
DB_SITE_NUM_EMPLOYEES                NEXT     *   |       CHARACTER            
DB_COMPANY_NUM_EMPLOYEES             NEXT     *   |       CHARACTER            
DB_FAX_NUMBER                        NEXT     *   |       CHARACTER            
DB_SIC_LINE_OF_BUSINESS              NEXT     *   |       CHARACTER            
DB_COMPANY_SIC_CODE                  NEXT     *   |       CHARACTER            
DB_COMPANY_SIC_DESCRIPTION           NEXT     *   |       CHARACTER            
EMEA_VERTICAL_MARKET                 NEXT     *   |       CHARACTER            
EMEA_VERTICAL_MARKET_SUB_GROUP       NEXT     *   |       CHARACTER            
POS_TRANSACTION_ID                   NEXT     *   |       CHARACTER            
POS_NET_PRICE                        NEXT     *   |       CHARACTER            

Record 1: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 2: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 3: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 4: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 5: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 6: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 7: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 8: Rejected - Error on table IMP_ECUST, column SALES_AGENT_ID.
ORA-01722: invalid number

Record 9: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 10: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 11: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 12: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 13: Rejected - Error on table IMP_ECUST, column SALES_AGENT_ID.
ORA-01722: invalid number

Record 14: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 15: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 16: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 17: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 18: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 19: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 20: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 21: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 22: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 23: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 24: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 25: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 26: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 27: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 28: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 29: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 30: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 31: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 32: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 33: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 34: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 35: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 36: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 37: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 38: Rejected - Error on table IMP_ECUST, column SALES_AGENT_ID.
ORA-01722: invalid number

Record 39: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 40: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 41: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 42: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 43: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 44: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 45: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 46: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 47: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 48: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 49: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 50: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 51: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 52: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 53: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 54: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 55: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 56: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 57: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 58: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 59: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 60: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 61: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 62: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 63: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 64: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 65: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 66: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 67: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 68: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 69: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 70: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 71: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 72: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 73: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 74: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 75: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 76: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 77: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 78: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 79: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 80: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 81: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 82: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 83: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 84: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 85: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 86: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 87: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 88: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 89: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 90: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 91: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 92: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 93: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 94: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number

Record 95: Rejected - Error on table IMP_ECUST, column CSC_COMPANY_ID.
ORA-01722: invalid number


Table IMP_ECUST:
  0 Rows successfully loaded.
  95 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:                  62694 bytes(9 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          1
Total logical records read:            95
Total logical records rejected:        95
Total logical records discarded:        0

Run began on Wed Aug 04 10:09:11 2004
Run ended on Wed Aug 04 10:09:14 2004

Elapsed time was:     00:00:03.60
CPU time was:         00:00:00.22

Taking the first rejected record as an example (they are all pretty similar):

P|EMEA GEO|EMEA NORTHERN EUROPE|FINLAND|FINLAND PUBLIC SECTOR|FINLAND CENTRAL GOVT|1619636|HELSINGIN SEURAKUNTAYHTYMÄ|1619636|HELSINGIN SEURAKUNTAYHTYMÄ|4331808|HELSINGIN SEURAKUNTAYHTYMÄ|2003Q4|44974|JUSSILA,JUKKA|||||||||PUBLIC SECTOR|CENTRAL GOVERNMENT|13041992|370.30000000000001

It seems that it could be related to the Ä character in the proceeding column, as all the records that fail seem to have Ä or Ö in the preceeding column.

The NLS_CHARACTER_SET of the DB is UTF8, and my environment is WE8ISO8859P1.

Any ideas?

Thanks
 
Sort by date Sort by votes
I think your problem may be your environment. I don't think WE8ISO8859P1 works too well with UTF8. Can you change your own environment to UTF8 or WE8ISO8859P19 ?
 
Upvote 0 Downvote
I'm in a windows environment. Actual setting for nls_lang is:

american_america.WE8ISO8859P1

I tried UTF8 and WE8ISO8859P19, neither worked.
 
Upvote 0 Downvote
Consider changing the fields that are rejecting to VARCHAR2. It does not appear that you will be doing any calculations on these fields.
 
Upvote 0 Downvote
Have you tried uploading just one record without the special character? Did this work ok?
 
Upvote 0 Downvote
Tried with one record. When I removed all the special characters, it loads no problem.

I don't think changing the fields to VARCHAR2 is going to fix the problem, since it seems the problem lies in the characters in the adjacent field.

 
Upvote 0 Downvote
Hi,
as the character sets of DB and frontend differ, you may have to add this line in your control file:
CHARACTERSET WE8ISO8859P1
hth
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

George L
  • Locked
  • Question
web app not saving "all" records suddenly
Replies
3
Views
363
carp
carp
Andrzejek
  • Locked
  • Question
Insert multiple records 1
Replies
9
Views
399
mikrom
mikrom
Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
381
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Pick latest records 1
Replies
2
Views
277
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Format a number - TO_CHAR() 2
Replies
5
Views
412
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top