Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    1

    Post Unanswered: Load Date in SQLLDR

    Hello,

    Am using SQL loader to load data from a dat file. I have a date field defined in the table and that field is having null value in the source file (.dat). when ever i try to load, the loader logs
    ------------------------------------------------------------------------------------
    Record 2: Rejected - Error on table TEMP_TAB, column MFR_DT.
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    ------------------------------------------------------------------------------------

    i feel we may have to set and attribute at the date field but am not able to figure it out.

    I would appricate your response towards the same with great pleasure.

    Thank you

    Regards

    Vasu

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Are you sure the field in the text extract is really null, or is it a series of blanks? If it's a fixed width file, the chances are it's been padded with blanks.

    Try using the NULLIF clause in the control file, ie:
    Code:
    position(003:012) DATE "YYYY-MM-DD" NULLIF your_date_field = BLANKS

  3. #3
    Join Date
    Dec 2002
    Posts
    1

    Arrow

    I find I only get this error when using direct and unrecoverable options in the ctl file..

    if i then append the .bad records they go in with no complaint

    weird!

Posting Permissions

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