-
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.
-
Please post:
1- sqlloader control file
2- printout of the file you are trying to load (1 row?)
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
data file one row:
|SWP129043|HEALTHNET OF CALIFORNIA|HEA26598|RECEIVE|20070723120745000000|2007-05-01|1.0|NetSaver 1500|000080.00|ASYESTER@HOTMAIL.COM|21||Y|SKYE|BRTYMAN|534-66-1543|1972-04-28|Received - Sent to Underwriting for review||2007-07-23|IFP|Pri|10853 CAWTLO ST. #12A ||NORTH HOLLYWOOD|CA|91602|354-349-2766|000000001|
ctl file:
LOAD DATA
INFILE "appdata.txt"
APPEND
INTO TABLE HEALTHNET_APPLICATION
fields terminated by '|'
TRAILING NULLCOLS
(
EHEALTH_REFERENCE_ID ,
CARRIER_APPLICATION_ID ,
CARRIER_NAME ,
ALLIANCE_ID ,
STATUS ,
STATUS_DATE Date "YYYY-MM-DD",
EFFECTIVE_DATE Date "YYYY-MM-DD",
TIER ,
PLAN_NAME ,
MONTHLY_PREMIUM ,
EMAIL ,
LOGICAL_PLAN_ID ,
EXTERNAL_AGENT_ID ,
PRIMARY_SUBSCRIBER ,
FIRST_NAME ,
LAST_NAME ,
SSN ,
BIRTH_DATE Date "YYYY-MM-DD",
MEMBER_STATUS ,
MEMBER_NOTES ,
APPLICATION_TYPE ,
SUBMIT_DATE ,
PHONE ,
ADDRESS_LINE_1 ,
ADDRESS_LINE_2 ,
CITY ,
ZIP_CODE ,
PRODUCTLINE ,
RELATIONSHIP_TO_PRIMARY ,
APPLICATION_NOTES
)
-
Would you mind in doing a quick test for me?
Please take out the leading pipe and try again.
so your data file will look like...
SWP129043|HEALTHNET OF CALIFORNIA|HEA26598|RECEIVE|20070723120745000000|2007-05-01|1.0|NetSaver 1500|000080.00|ASYESTER@HOTMAIL.COM|21||Y|SKYE|BRTYMAN|534-66-1543|1972-04-28|Received - Sent to Underwriting for review||2007-07-23|IFP|Pri|10853 CAWTLO ST. #12A ||NORTH HOLLYWOOD|CA|91602|354-349-2766|000000001|
Last edited by PAVB; 07-26-2007 at 07:10 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
Ok... that's a different issue, would you mind posting desc of HEALTHNET_APPLICATION table?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
desc provided
SQL> desc HEALTHNET_APPLICATION
Name Null? Type
----------------------------------------- -------- ----------------------------
EHEALTH_REFERENCE_ID NUMBER
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 BLOB
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 EHEALTH_REFERENCE_ID is of the NUMBER datatype.
Oracle can't load the value "SWP129043" in there, that's why you are getting error ORA-01722: invalid number
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
I would love to baby seat you all the way long but, a) a have some real work to do and, b) you are getting too spoiled so, better for you to start doing some heavy lifting by yourself.
You already saw what the troubleshooting procedure is, look at the data, look at the table and, look at the control file looking for inconsistencies.
A hint?... divide your big problem into small problems that can be more easily trobleshoot and solved; I would start loading a table where you have only the first column, loading a file that populates only that column. Once you get it working start adding columns in both the table and the input file until everything is working as desired.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|