Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Question Unanswered: SQLLDR Control File - how to handle invalid time-stamp i.e. 200904279999

    I have some large text files to load into Oracle 9. I've been using SQLLDR successfully except for a few hundred fields out of millions have an invalid time portion of the date time stamp.

    For example the format is a YYYYMMDDHH24MI, but for some records where the time is invalid, it has been replaced with 9999 leaving 200904279999. The loader fails because the hour/minutes are invalid.

    "Record 71484: Rejected - Error on table TBL1, column STARTDATE.
    ORA-01850: hour must be between 0 and 23"

    Is there a way to prevent the 9999 portion from being used ... is there a way to null it out? I still want to keep the date portion.

    I am currently using this:
    STARTDATE POSITION(61:72) DATE 'YYYYMMDDHH24MI' NULLIF STARTDATE = BLANKS,

    TIA

    ps. I would do a find/replace in the text file but there are other field where "9999" is valid.

  2. #2
    Join Date
    Apr 2009
    Posts
    4

    Lightbulb

    I managed to use EditPadLite (opens large text files instantly) to open the 500MB text file and do a search and replace of the "9999" that were related to the hour/min because they were always followed by a certain string.

    I am still curious if there is a more elegant solution.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You could set the ERRORS parameter very high to have SQL*Loader continue on errors and write the wrong records into a separate file.

    For details see the manual:

    ERRORS parameter:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_params.htm#sthref573

    BAD parameter:
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_params.htm#sthref543

  4. #4
    Join Date
    Apr 2009
    Posts
    4
    Yes, that works too ... I set ERRORS=1000 and then viewed the BAD file, cleaned it up and uploaded it.

    Thanks

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am still curious if there is a more elegant solution.
    Modify the software which creates the file so that rather than "9999" it produces "0000".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Apr 2009
    Posts
    4
    >Modify the software which creates the file so that rather than "9999" it produces "0000".
    If only I had access ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •