sqlldr ORA-01861: literal does not match format string
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: sqlldr ORA-01861: literal does not match format string

Hybrid View

  1. #1
    Join Date
    Mar 2007
    Posts
    48

    Unhappy 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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Mar 2007
    Posts
    48

    Unhappy

    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
    )

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  5. #5
    Join Date
    Mar 2007
    Posts
    48

    Unhappy 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

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  7. #7
    Join Date
    Mar 2007
    Posts
    48

    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

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  9. #9
    Join Date
    Mar 2007
    Posts
    48

    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

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  



Click Here to Expand Forum to Full Width