Home » RDBMS Server » Server Utilities » ORA-01722 (CSV to Oracle using SQLLDR)
ORA-01722 [message #669802] Mon, 14 May 2018 10:04 Go to previous message
Sunith5945
Messages: 1
Registered: May 2018
Junior Member
can anyone help me finding the solution, Have tried so many recommendations but no use.
this is my Control File

load data
infile 'Table2013.txt'
into table table2013
fields terminated by "~:~" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
PAT_AGE_FILLED,
PC_PAT_LNAME,PC_PAT_FNAME,PC_PAT_MNAME,PC_PAT_NAME_PREFIX,PC_PAT_NAME_SUFFIX,PC_PAT_ADDR1,PC_PAT_ADDR2,PC_PAT_CITY,PC_PAT_ZIP_PLUS4,P C_PAT_TELEPHONE,PC_PAT_ANIMAL_NAME,PC_PAT_FNAME_DISPLAY,PC_PAT_LNAME_DISPLAY,PC_PAT_FNAME_STAN,PC_PAT_LNAME_STAN,PC_PAT_ADDR1_DISPLAY ,PC_PAT_ADDR2_DISPLAY,PC_PAT_ADDR1_STAN,PC_PAT_CITY_DISPLAY,PC_PAT_BIRTH_YEAR,PC_PAT_BIRTH_MMDD,NDC_ACRONYM_CATEGORY,NDC_NON_LIQUID,D ISCLOSE_DEANO,DISCLOSE_NABP,DISCLOSE_RPID,NDC_LABEL_NAME,PATIENT_ZIP_COUNTY,
PATIENT_ZIP_COUNTY_FIPS ,
PRESCRIBER_ZIP_COUNTY ,PRESCRIBER_ZIP_COUNTY_FIPS,DISPENSER_ZIP,DISPENSER_ZIP_COUNTY,DISPENSER_ZIP_COUNTY_FIPS,
PATIENT_BIRTH_DATE DATE "MON-DD-YYYY" ":PATIENT_BIRTH_DATE",
DATE_FILLED DATE "MON-DD-YYYY" ":DATE_FILLED",
DATE_RX_WRITTEN DATE "MON-DD-YYYY" ":DATE_RX_WRITTEN",
SENDER_DATE DATE "MON-DD-YYYY" ":SENDER_DATE",
UPDATE_DATE DATE "MON-DD-YYYY" ":UPDATE_DATE",
DEA_REGDATE DATE "MON-DD-YYYY" ":DEA_REGDATE",
DEA_EXPDATE DATE "MON-DD-YYYY" ":DEA_EXPDATE"
)...
error message
value used for ROWS parameter changed from 64 to 8
Record 1: Rejected - Error on table Table2013, column PAT_AGE_FILLED.
ORA-01722: invalid number

Record 2: Rejected - Error on table Table2013, column PAT_AGE_FILLED.
ORA-01722: invalid number


Record 50: Rejected - Error on table Table2013, column PAT_AGE_FILLED.
ORA-01722: invalid number

Record 51: Rejected - Error on table Table2013, column PAT_AGE_FILLED.
ORA-01722: invalid number


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table Table2013:
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.
 
Read Message
Read Message
Read Message
Previous Topic: Bulk insert with SQL*Loader fails to abort inserts on index errors
Next Topic: SQL Loader
Goto Forum:
  


Current Time: Mon Apr 06 17:17:01 CDT 2020