Hi All

I am trying to insert the record using SQLLDR through .csv file. i have created the proper control file ,temp table and try to insert the record.
but while inserting the record i am getting "invalid number" error ,though i have check my csv file , the column which throwing the error contain number only , no specail charcter.

Record 1: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number
Record 2: Rejected - Error on table T_MEAS_38_TMP, column RPT_TN.
ORA-01722: invalid number

But when i try to change the datatype from number to varchar2 with size compartivaly more then it allows me to insert the record for that column and error sift to other column having number datatype.
Plz help me out 4 this and how can i ressolve this issue.

I cant use varchar2 for each n evry column.


Here are the details of my control file ,table and .csv file
Table Defination
CREATE TABLE T_MEAS_38_TMP
(
ACNA varchar2(4),
YEARMO number(15),
STATE varchar2(24),
METRO varchar2(25),
DPID number(15),
NUM number(2),
DEN number(2),
RETAIL number(2),
main_tn number(30),
so_nbr varchar2(20),
RPT_TN number(30),
cls_name varchar2(19),
stc varchar2(12),
PROD_NM varchar2(30),
RPTD_NM varchar2(30),
CKTID varchar2(50),
cac varchar2(45),
busid varchar2(10),
seg_cd varchar2(10),
rpt_nbr varchar2(10),
mctr varchar2(12),
tr_type varchar2(15),
disp number(20),
cat number(11),
tr_cd varchar2(4),
recdt number(11),
rec_tm number(11),
clrdt number,
clr_tm number(8),
aclod number(11),
act_dur number(11),
appt_cd number(5),
req varchar2(10),
ttclsfn varchar2(5),
fcas number(3),
icase number(10),
repeat number(1),
fvi number(1),
oos number(1),
dsl number(1),
storage_dt number(15),
unep number(8),
mcn varchar2(11),
assgn_mcn varchar2(11)
)
tablespace RTW_APP_DAT
;


Control file...
LOAD DATA
INFILE 'C:\test\38D_Measure\re038d_sta_une.csv'
BADFILE 'C:\test\38D_Measure\re038d_sta_une.bad'
DISCARDFILE 'C:\test\38D_Measure\re038d_sta_une.dsc'
insert
INTO TABLE t_meas_38_tmp
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ACNA,
YEARMO,
STATE,
METRO,
DPID,
NUM,
DEN,
RETAIL,
main_tn,
so_nbr,
RPT_TN,
cls_name,
stc,
PROD_NM,
RPTD_NM,
CKTID,
cac,
busid,
seg_cd,
rpt_nbr,
mctr,
tr_type,
disp,
cat,
tr_cd,
recdt,
rec_tm,
clrdt,
clr_tm,
aclod,
act_dur,
appt_cd,
req,
ttclsfn,
fcas,
icase,
repeat,
fvi,
oos,
dsl,
storage_dt,
unep,
mcn,
assgn_mcn
)

.csv file example record
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1
99R 200902 IL CHICAGO 228 0 1 1

this is only few line of the csv file


Thanks in advance

First of all, you have specified that FIELDS TERMINATED BY "," but your sample data do not follow that statement (and result in rejecting all sample records).

Secondly, even when this trivial error is fixed, none of the records fails because of the error you have mentioned (invalid number).

Therefore, would you mind to post sample data which reflect your question?

HI Littlefoot,
i have remove the Field Terminated by "," with " " in control file.
On removing field terminated ,
Here is the .csv excel (see the attachment) for excel .csv file and log file.

Thanks for you Quick responce

please find the log file on removing Field Terminnated by "," with " "
Also see the sample data of .csv file i have appended on5 to 6 rows.

-------------LOG FILE-----------

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Jul 15 18:42:45 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: re038d_sta_une.ctl
Data File: C:\test\38D_Measure\re038d_sta_une.csv
Bad File: C:\test\38D_Measure\re038d_sta_une.bad
Discard File: C:\test\38D_Measure\re038d_sta_une.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 T_MEAS_38_TMP, 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
------------------------------ ---------- ----- ---- ---- ---------------------
ACNA FIRST * WHT O( ) CHARACTER
YEARMO NEXT * WHT O( ) CHARACTER
STATE NEXT * WHT O( ) CHARACTER
METRO NEXT * WHT O( ) CHARACTER
DPID NEXT * WHT O( ) CHARACTER
NUM NEXT * WHT O( ) CHARACTER
DEN NEXT * WHT O( ) CHARACTER
RETAIL NEXT * WHT O( ) CHARACTER
MAIN_TN NEXT * WHT O( ) CHARACTER
SO_NBR NEXT * WHT O( ) CHARACTER
RPT_TN NEXT * WHT O( ) CHARACTER
CLS_NAME NEXT * WHT O( ) CHARACTER
STC NEXT * WHT O( ) CHARACTER
PROD_NM NEXT * WHT O( ) CHARACTER
RPTD_NM NEXT * WHT O( ) CHARACTER
CKTID NEXT * WHT O( ) CHARACTER
CAC NEXT * WHT O( ) CHARACTER
BUSID NEXT * WHT O( ) CHARACTER
SEG_CD NEXT * WHT O( ) CHARACTER
RPT_NBR NEXT * WHT O( ) CHARACTER
MCTR NEXT * WHT O( ) CHARACTER
TR_TYPE NEXT * WHT O( ) CHARACTER
DISP NEXT * WHT O( ) CHARACTER
CAT NEXT * WHT O( ) CHARACTER
TR_CD NEXT * WHT O( ) CHARACTER
RECDT NEXT * WHT O( ) CHARACTER
REC_TM NEXT * WHT O( ) CHARACTER
CLRDT NEXT * WHT O( ) CHARACTER
CLR_TM NEXT * WHT O( ) CHARACTER
ACLOD NEXT * WHT O( ) CHARACTER
ACT_DUR NEXT * WHT O( ) CHARACTER
APPT_CD NEXT * WHT O( ) CHARACTER
REQ NEXT * WHT O( ) CHARACTER
TTCLSFN NEXT * WHT O( ) CHARACTER
FCAS NEXT * WHT O( ) CHARACTER
ICASE NEXT * WHT O( ) CHARACTER
REPEAT NEXT * WHT O( ) CHARACTER
FVI NEXT * WHT O( ) CHARACTER
OOS NEXT * WHT O( ) CHARACTER
DSL NEXT * WHT O( ) CHARACTER
STORAGE_DT NEXT * WHT O( ) CHARACTER
UNEP NEXT * WHT O( ) CHARACTER
MCN NEXT * WHT O( ) CHARACTER
ASSGN_MCN NEXT * WHT O( ) CHARACTER

value used for ROWS parameter changed from 64 to 22
Record 1: Rejected - Error on table T_MEAS_38_TMP, column ACNA.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 3: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 4: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 5: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 6: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 7: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 8: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 9: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 10: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 11: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number

Record 12: Rejected - Error on table T_MEAS_38_TMP, column YEARMO.
ORA-01722: invalid number


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table T_MEAS_38_TMP:
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: 249744 bytes(22 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 67
Total logical records rejected: 51
Total logical records discarded: 0

Run began on Wed Jul 15 18:42:45 2009
Run ended on Wed Jul 15 18:42:46 2009

Elapsed time was: 00:00:01.14
CPU time was: 00:00:00.09
==================================

----------SAMPLE DATA .csv -----------------
ACNA DATA_MONTH STATE METRO_AREA TRACKING_ID NUMERATOR DENOMINATOR RETAIL_IND MAIN_TN SVC_ORDER REPT_TEL_NUM COS_NAME SERV_TYPE_CD PROD_NM RPTD_PROD_NM CKTID CAC BUS_ID BUS_SEG_CD REPORT_NUM MCTR TRBL_REPO_TYPE DISP_CODE CAT_CODE TRBL_CODE RECDT REC_TM CLRDT CLR_TM ACLOD ACTUAL_DUR MISSD_APT_CD REQ_FLAG TTCLSFN FCAS ICASE REPEAT FIELD_VISIT_IND OOS DSL_SHR_IND STORAGE_DT UNEP_IND
99R 200902 IL CHICAGO 228 0 1 1 7735857336 773/585/7336 RESIDENCE 4 NAP2DT7C PL16RN06 1 4 1 2122009 709 2132009 360 2142009 1091 0 2 471 0 0 1 1 0 2152009 0
99R 200902 IL CHICAGO 228 0 1 1 3122269536 312/226/9536 BUSINESS 5 NAP2EB4C PL179W13 1 4 1 2272009 631 2272009 950 2272009 319 0 2 471 0 0 1 1 0 2282009 0
99R 200902 IL CHICAGO 228 0 1 1 7732710065 773/271/0065 RESIDENCE 4 NAP2DY2C PL174C28 1 4 1 2222009 938 2232009 597 2232009 1099 1 2 471 0 0 1 1 0 2242009 0
99R 200902 IL CHICAGO 228 0 1 1 7737212875 773/721/2875 BUSINESS 5 NAN2YH7C PL178002 1 4 1 2262009 499 2262009 646 2262009 147 0 2 471 0 0 1 1 0 2272009 0
99R 200902 IL CHICAGO 228 0 1 1 7735851662 773/585/1662 BUSINESS 5 NKP2EW2X PL16RM37 3 4 1 2122009 702 2132009 360 2142009 1098 0 2 471 0 0 1 0 0 2152009 0
99R 200902 IL CHICAGO 228 0 1 1 773/745/4550 RESIDENCE 4 NAP2BZ8C PL174255 1 7 1 2212009 1081 2222009 850 2222009 1209 0 2 300 0 0 1 1 0 2232009 0
99R 200902 IL CHICAGO 228 0 1 1 773/978/8771 RESIDENCE 4 NAP2DX4C PL172R43 1 7 1 2202009 464 2202009 910 2202009 446 0 2 471 0 0 1 1 0 2222009 0
99R 200902 IL CHICAGO 228 0 1 1 7736386343 773/638/6343 RESIDENCE 4 NUA2LT9N CS PL171065 5 5 1 2182009 1124 2182009 1125 2182009 1 0 2 193 0 0 0 0 0 2192009 0
99R 200902 IL CHICAGO 228 0 1 1 7733425844 773/342/5844 RESIDENCE 4 NUJ2TP7S CS PL178Z76 1 4 1 2262009 1038 2272009 829 2272009 1231 0 N 2 471 0 0 1 1 0 2282009 0

Post Operating System (OS) name & version for DB server system.
Post Oracle version to 4 decimal place of database s/w from v$version.

It appears 44 columns in table.
>99R 200902 IL CHICAGO 228 0 1 1 7735857336 773/585/7336 RESIDENCE 4 NAP2DT7C PL16RN06 1 4 1 2122009 709 2132009 360 2142009 1091 0 2 471 0 0 1 1 0 2152009 0
I counted only 33 spaces/fields in row above. Why?

>i have created the proper control file ,temp table
Then how do you explain the errors & data discrepancy?

Either you or Oracle are in error.
Which is more likely?