sql*loader problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: sql*loader problem

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    Hi friends,
    My control file is as follows:
    load data
    infile *
    append
    into table system.table_a
    FIELDS TERMINATED BY ","
    (
    d,
    e date "dd-mon-yyyy hh24:mi:ss",
    f
    )
    begindata
    3607551298,'30-jan-2050 13:30:00',3111188,
    3607551428,30-jan-2050 13:30:00,2160118,
    3607551484,30-jan-2050 13:30:00,3934798

    When I try to load, it is giving me the following error:

    Table SYSTEM.TABLE_A, loaded from every logical record.
    Insert option in effect for this table: APPEND

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    D FIRST * , CHARACTER
    E NEXT * , DATE dd-mon-yyyy hh24:mi:ss
    F NEXT * , CHARACTER

    Record 1: Rejected - Error on table SYSTEM.TABLE_A, column D.
    ORA-01847: day of month must be between 1 and last day of month

    Record 2: Rejected - Error on table SYSTEM.TABLE_A, column D.
    ORA-01847: day of month must be between 1 and last day of month

    Record 3: Rejected - Error on table SYSTEM.TABLE_A, column D.
    ORA-01847: day of month must be between 1 and last day of month


    Table SYSTEM.TABLE_A:
    0 Rows successfully loaded.
    3 Rows 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.

    The error is obviously wrong because even when I changed the date the same error occured.

    Can someone help me with the solution?
    thanks
    manjunath

    my mistake!!!!
    the table column D had a date datatype too.
    Thanks

    [Edited by manjunathk on 01-23-2001 at 06:11 PM]

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    When I changed your data to:
    3607551298,'30-jan-2050 13:30:00',3111188
    3607551428,30-jan-2050 13:30:00,2160118
    3607551484,30-jan-2050 13:30:00,3934798

    I got:
    Record 1: Rejected - Error on table TABLE_A, column E.
    ORA-01858: a non-numeric character was found where a numeric was expected


    Table TABLE_A:
    2 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.

    When I changed your control file to:
    load data
    infile *
    append
    into table table_a
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "'"
    (
    d,
    e date "dd-mon-yyyy hh24:mi:ss",
    f
    )
    begindata
    3607551298,'30-jan-2050 13:30:00',3111188
    3607551428,30-jan-2050 13:30:00,2160118
    3607551484,30-jan-2050 13:30:00,3934798

    I got:
    Table TABLE_A:
    3 Rows successfully loaded.
    0 Rows 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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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