Home » RDBMS Server » Server Utilities » ORA-01722 (CSV to Oracle using SQLLDR)
ORA-01722 [message #669802] Mon, 14 May 2018 10:04 Go to next 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.
Re: ORA-01722 [message #669803 is a reply to message #669802] Mon, 14 May 2018 10:14 Go to previous messageGo to next message
cookiemonster
Messages: 13120
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the error speaks for itself really.

If you need help fixing it then you need post some example data you're trying to load.
Re: ORA-01722 [message #669823 is a reply to message #669802] Tue, 15 May 2018 07:49 Go to previous message
joy_division
Messages: 4857
Registered: February 2005
Location: East Coast USA
Senior Member
It works for me.
Previous Topic: Bulk insert with SQL*Loader fails to abort inserts on index errors
Next Topic:
Goto Forum:
  


Current Time: Sat May 26 12:49:20 CDT 2018