OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » ORA-01722 (CSV to Oracle using SQLLDR)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01722 [message #669802]

Mon, 14 May 2018 10:04

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.

Report message to a moderator

[Message index]

		ORA-01722 By: Sunith5945 on Mon, 14 May 2018 10:04
		Re: ORA-01722 By: cookiemonster on Mon, 14 May 2018 10:14
		Re: ORA-01722 By: joy_division on Tue, 15 May 2018 07:49

Previous Topic:	Bulk insert with SQL*Loader fails to abort inserts on index errors
Next Topic:	SQL Loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:00:21 CDT 2024