sqlldr ORA-01861: literal does not match format string
I am having such a hard time getting 5 rows loaded. There are It seems to complain about the date format. I had it defined as date and the date is like "2007-05-01" so in sqlldr ctl file, i do
STATUS_DATE DATE "YYYY-MM-DD"
and keep on getting the following error:
value used for ROWS parameter changed from 64 to 33
Record 1: Rejected - Error on table HEALTHNET_APPLICATION, column STATUS_DATE.
ORA-01861: literal does not match format string
Table HEALTHNET_APPLICATION:
0 Rows successfully loaded.
1 Row 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: 255420 bytes(33 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0
Can anyone help with the date format? Thanks.
tried but did not work :(
I got the output error:
SQL*Loader: Release 10.1.0.4.0 - Production on Thu Jul 26 16:19:11 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Control File: appdata2.ctl
Data File: appdata2.txt
Bad File: appdata2.bad
Discard File: none specified
(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 HEALTHNET_APPLICATION, 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
------------------------------ ---------- ----- ---- ---- ---------------------
EHEALTH_REFERENCE_ID FIRST * | CHARACTER
CARRIER_APPLICATION_ID NEXT * | CHARACTER
CARRIER_NAME NEXT * | CHARACTER
ALLIANCE_ID NEXT * | CHARACTER
STATUS NEXT * | CHARACTER
STATUS_DATE NEXT * | DATE DD-MON-YY
EFFECTIVE_DATE NEXT * | DATE DD-MON-YY
TIER NEXT * | CHARACTER
PLAN_NAME NEXT * | CHARACTER
MONTHLY_PREMIUM NEXT * | CHARACTER
EMAIL NEXT * | CHARACTER
LOGICAL_PLAN_ID NEXT * | CHARACTER
EXTERNAL_AGENT_ID NEXT * | CHARACTER
PRIMARY_SUBSCRIBER NEXT * | CHARACTER
FIRST_NAME NEXT * | CHARACTER
LAST_NAME NEXT * | CHARACTER
SSN NEXT * | CHARACTER
BIRTH_DATE NEXT * | DATE DD-MON-YY
MEMBER_STATUS NEXT * | CHARACTER
MEMBER_NOTES NEXT * | CHARACTER
APPLICATION_TYPE NEXT * | CHARACTER
SUBMIT_DATE NEXT * | DATE DD-MON-YY
PHONE NEXT * | CHARACTER
ADDRESS_LINE_1 NEXT * | CHARACTER
ADDRESS_LINE_2 NEXT * | CHARACTER
CITY NEXT * | CHARACTER
ZIP_CODE NEXT * | CHARACTER
PRODUCTLINE NEXT * | CHARACTER
RELATIONSHIP_TO_PRIMARY NEXT * | CHARACTER
APPLICATION_NOTES NEXT * | CHARACTER
value used for ROWS parameter changed from 64 to 33
Record 1: Rejected - Error on table HEALTHNET_APPLICATION, column EHEALTH_REFERENCE_ID.
ORA-01722: invalid number
Table HEALTHNET_APPLICATION:
0 Rows successfully loaded.
1 Row 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: 255420 bytes(33 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0
Run began on Thu Jul 26 16:19:11 2007
Run ended on Thu Jul 26 16:19:11 2007
Elapsed time was: 00:00:00.04
CPU time was: 00:00:00.00
changed first column to varchar still error
SQL> desc HEALTHNET_APPLICATION
Name Null? Type
----------------------------------------- -------- ----------------------------
EHEALTH_REFERENCE_ID VARCHAR2(10)
CARRIER_APPLICATION_ID VARCHAR2(80)
CARRIER_NAME VARCHAR2(80)
ALLIANCE_ID VARCHAR2(12)
STATUS VARCHAR2(20)
STATUS_DATE DATE
EFFECTIVE_DATE DATE
TIER NUMBER
PLAN_NAME VARCHAR2(80)
MONTHLY_PREMIUM NUMBER
EMAIL VARCHAR2(100)
LOGICAL_PLAN_ID NUMBER
EXTERNAL_AGENT_ID VARCHAR2(40)
PRIMARY_SUBSCRIBER VARCHAR2(1)
FIRST_NAME VARCHAR2(30)
LAST_NAME VARCHAR2(40)
SSN VARCHAR2(11)
BIRTH_DATE DATE
MEMBER_STATUS VARCHAR2(30)
MEMBER_NOTES VARCHAR2(4000)
APPLICATION_TYPE VARCHAR2(20)
SUBMIT_DATE DATE
PHONE VARCHAR2(12)
ADDRESS_LINE_1 VARCHAR2(80)
ADDRESS_LINE_2 VARCHAR2(80)
CITY VARCHAR2(40)
ZIP_CODE VARCHAR2(12)
PRODUCTLINE VARCHAR2(12)
RELATIONSHIP_TO_PRIMARY VARCHAR2(8)
APPLICATION_NOTES BLOB
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EHEALTH_REFERENCE_ID FIRST * | CHARACTER
CARRIER_APPLICATION_ID NEXT * | CHARACTER
CARRIER_NAME NEXT * | CHARACTER
ALLIANCE_ID NEXT * | CHARACTER
STATUS NEXT * | CHARACTER
STATUS_DATE NEXT * | DATE YYYY-MM-DD
EFFECTIVE_DATE NEXT * | DATE YYYY-MM-DD
TIER NEXT * | CHARACTER
PLAN_NAME NEXT * | CHARACTER
MONTHLY_PREMIUM NEXT * | CHARACTER
EMAIL NEXT * | CHARACTER
LOGICAL_PLAN_ID NEXT * | CHARACTER
EXTERNAL_AGENT_ID NEXT * | CHARACTER
PRIMARY_SUBSCRIBER NEXT * | CHARACTER
FIRST_NAME NEXT * | CHARACTER
LAST_NAME NEXT * | CHARACTER
SSN NEXT * | CHARACTER
BIRTH_DATE NEXT * | DATE YYYY-MM-DD
MEMBER_STATUS NEXT * | CHARACTER
MEMBER_NOTES NEXT * | CHARACTER
APPLICATION_TYPE NEXT * | CHARACTER
SUBMIT_DATE NEXT * | DATE YYYY-MM-DD
PHONE NEXT * | CHARACTER
ADDRESS_LINE_1 NEXT * | CHARACTER
ADDRESS_LINE_2 NEXT * | CHARACTER
CITY NEXT * | CHARACTER
ZIP_CODE NEXT * | CHARACTER
PRODUCTLINE NEXT * | CHARACTER
RELATIONSHIP_TO_PRIMARY NEXT * | CHARACTER
APPLICATION_NOTES NEXT * | CHARACTER
Record 1: Rejected - Error on table HEALTHNET_APPLICATION, column STATUS_DATE.
ORA-01861: literal does not match format string
Table HEALTHNET_APPLICATION:
0 Rows successfully loaded.
1 Row 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: 7710 bytes(1 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0
Run began on Thu Jul 26 18:38:47 2007
Run ended on Thu Jul 26 18:38:47 2007
Elapsed time was: 00:00:00.04