Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: SQL*Loader

  1. #1
    Join Date
    Oct 2003
    Posts
    14

    Unanswered: SQL*Loader

    I am using SQL*Loader to populate a Table but some of the date fields being imported are null, however I get the following message in my Log file when trying to run the SQL*Loader program

    Record 1: Rejected - Error on table SYS.XX_MTL_SYS_ITEMS_INTERFACE, column START_DATE_ACTIVE.
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    Snippet of my control file :
    LOAD DATA
    INFILE 'ITEM.TXT'
    --TRUNCATE
    INTO TABLE SYS.XX_MTL_SYS_ITEMS_INTERFACE
    FIELDS TERMINATED BY "|"
    (LAST_UPDATE_DATE DATE 'yyyy-mm-dd'
    ,CREATION_DATE DATE 'yyyy-mm-dd'
    ,ENABLED_FLAG
    ,START_DATE_ACTIVE DATE 'yyyy-mm-dd'

    Any help would be appreciated

  2. #2
    Join Date
    Oct 2003
    Posts
    26

    Re: SQL*Loader

    Hi,

    Try using FIELDS TERMINATED BY "|" TRAILING NULLCOLS and let us know if this solves the problem

    If u still face any errors, post the same & the bad records

    HTH
    Arvind

  3. #3
    Join Date
    Oct 2003
    Posts
    14
    Unfortunately Trailing NullColls did not work:

    This is the first record which gets rejected

    2003/09/27|1990/01/01|N| |2000/09/26

    The fourth field is the Active_date which gets rejected

    Thanks
    Mark

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Just wondering...

    Why is the date format in the control file "yyyy--mm--dd", and not "yyyy/mm/dd" (2000/09/26) ?

    CVM.

  5. #5
    Join Date
    Oct 2003
    Posts
    14
    I have tried both ways with the - and \ separator and neither works. I have checked the column within the Table and nulls are allowed so I'm stuck.

    Thanks for assisting
    Mark

  6. #6
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by solent
    I have tried both ways with the - and \ separator and neither works. I have checked the column within the Table and nulls are allowed so I'm stuck.

    Thanks for assisting
    Mark
    One last shot...

    Have you tried using double quotes to indicate the format ?

    LAST_UPDATE_DATE DATE "yyyy/mm/dd"

    CVM.

  7. #7
    Join Date
    Oct 2003
    Posts
    14
    I tried putting the double quotes around but no luck. Can a field with a Date format accept Null fields or do I somehow have to use the TO_DATE function within the Control file and if so, how ?

    Thanks
    Mark

  8. #8
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    One very last shot.

    As you are mentioning that the last date (column "Active_date ") gets rejected, you should check what characters (might be non-printable characters) follow your last date.

    So, check for anything that follows 2000/09/26 and is not a carriage return/line feed.

    2003/09/27|1990/01/01|N| |2000/09/26????

    I remember a problem I had a while ago. I didn't notice that my row actually terminated with NULL (ascii 0), followed by a carriage return/line feed. When I removed the "null" character, everything worked fine. You can check these "unprintable" character with any editor that allows "hexadecimal" edit.

    Maybe you can upload a sample of your file...

    CVM.

  9. #9
    Join Date
    Oct 2003
    Posts
    14
    I appreciate the help you guys are giving me.

    I am enclosing a sample file with four records to use with the control file i described in the previous post.
    Attached Files Attached Files

  10. #10
    Join Date
    Oct 2003
    Posts
    12

    Smile Re: SQL*Loader

    Hi,

    Use 'RRRR' instead of 'YYYY' in the date format which should solve the problem.

    - Rohit.

    Originally posted by solent
    I am using SQL*Loader to populate a Table but some of the date fields being imported are null, however I get the following message in my Log file when trying to run the SQL*Loader program

    Record 1: Rejected - Error on table SYS.XX_MTL_SYS_ITEMS_INTERFACE, column START_DATE_ACTIVE.
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    Snippet of my control file :
    LOAD DATA
    INFILE 'ITEM.TXT'
    --TRUNCATE
    INTO TABLE SYS.XX_MTL_SYS_ITEMS_INTERFACE
    FIELDS TERMINATED BY "|"
    (LAST_UPDATE_DATE DATE 'yyyy-mm-dd'
    ,CREATION_DATE DATE 'yyyy-mm-dd'
    ,ENABLED_FLAG
    ,START_DATE_ACTIVE DATE 'yyyy-mm-dd'

    Any help would be appreciated

  11. #11
    Join Date
    Oct 2003
    Posts
    14
    Sorry to say even that did not work (Setting the year format to RRRR instead of YYYY)

    I cannot find any documentation or indication what could be causing this from Oracle online or searching via the Web.

    Quite frustrating really.

    Thanks for the help
    Mark

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Am I wrong or is the 4th column a NULL and the last column is the 5th
    column

    2003/09/27|1990/01/01|N| |2000/09/26


    hth
    Gregg

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296

    Thumbs up

    Originally posted by gbrabham
    Am I wrong or is the 4th column a NULL and the last column is the 5th
    column

    2003/09/27|1990/01/01|N| |2000/09/26
    hth
    Gregg
    I agree with greg.
    ALSO, it looks more like a TAB than SPACE.
    Please supply a description of the table.

    However, shouldn't it load the NULL into the date field anyways?

    Try this, control should look like:
    PHP Code:
    LOAD DATA
    INFILE 
    'ITEM.TXT'
    --TRUNCATE
    INTO TABLE SYS
    .XX_MTL_SYS_ITEMS_INTERFACE
    FIELDS TERMINATED BY 
    '\t' optionally enclosed by '|' TRAILING NULLCOLS
    (LAST_UPDATE_DATE DATE 'yyyy/mm/dd',
    CREATION_DATE DATE 'yyyy/mm/dd',
    ENABLED_FLAG CHAR,
    dummy1 filler,
    START_DATE_ACTIVE DATE 'yyyy/mm/dd'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Oct 2003
    Location
    INDIA
    Posts
    7

    Re: SQL*Loader

    Originally posted by solent
    I am using SQL*Loader to populate a Table but some of the date fields being imported are null, however I get the following message in my Log file when trying to run the SQL*Loader program

    Record 1: Rejected - Error on table SYS.XX_MTL_SYS_ITEMS_INTERFACE, column START_DATE_ACTIVE.
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    Snippet of my control file :
    LOAD DATA
    INFILE 'ITEM.TXT'
    --TRUNCATE
    INTO TABLE SYS.XX_MTL_SYS_ITEMS_INTERFACE
    FIELDS TERMINATED BY "|"
    (LAST_UPDATE_DATE DATE 'yyyy-mm-dd'
    ,CREATION_DATE DATE 'yyyy-mm-dd'
    ,ENABLED_FLAG
    ,START_DATE_ACTIVE DATE 'yyyy-mm-dd'

    Any help would be appreciated

    Hi I got the problem.

    the record which gets rejected is
    date|date|N| |date
    now the problem is that there is an extra field in between
    first is date then we have a pipe to deliminate then we have a date and then again a pipe then we have value N then we have pipe then a space so loader trys to load that space to date field and throws error.

    I feel if u correct the datafile, it will solve the problem.
    All the Best

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    what?

    just use the FILLER line I provided in my example.
    All will be well.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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