Sqlldr importing date/time problems
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Sqlldr importing date/time problems

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    46

    Question sqlldr data/time problems

    Hi Everyone,

    I'm trying to load date/time data into a table. The data is formatted in one of two ways:
    1. Exact Date/Time
    2. A day start/end columns.

    Oracle is rejecting all records that don't use the exact date/time format. I've specified Default NULL in the create table statement, but it seems to be ignored. What am I doing wrong?



    --Create Table statement
    CREATE TABLE TIMES
    (
    PRIME_ID NUMBER(17) not null,
    A_SEQ_NO NUMBER(10),
    STARTDATE DATE DEFAULT null,
    ENDDATE DATE DEFAULT null,
    DAY_START NUMBER(2),
    DAY_END NUMBER(2)
    );



    --Control File and example data
    unrecoverable load data
    infile *
    append
    into table TIMES
    (
    PRIME_ID position (1:1) integer external,
    A_SEQ_NO position (2:3) integer external,
    STARTDATE position (5:23) date "yyyy-mm-dd hh24:mi:ss",
    ENDDATE position (25:43) date "yyyy-mm-dd hh24:mi:ss",
    DAY_START position (45:46) integer external,
    DAY_END position (48:49) integer external
    )

    begindata
    5 1 0 0
    5 2 4 5
    6 1 0 0
    6 2 5 9
    6 3 0 0
    8 1985.01.01 00:00:00 2001.12.04 00:00:00
    (EDIT COMMENT: The data is not being displayed correctly, but the data is in the correct position. I.E. No overlapping data.)

    --Error Messages
    Error Message:
    Record 1: Rejected - Error on table T_RC_TIMES, column STARTDATE
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    Record 2: Rejected - Error on table T_RC_TIMES, column STARTDATE
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0


    [Edited by pfc on 10-08-2001 at 04:53 AM]
    ora_newbie@yahoo.com

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    since you specify the position of all your fields in your data, Oracle expects to find each line with the format :

    PRIME_ID A_SEQ_NO STARTDATE ENDDATE DAY_START DAY_END

    the fact that you have a NULL default value does not change anything to that, Oracle tries to insert your data, and expects a date in the 3rd and 4th fields, which is not the case ...

    you have heterogeneous data, and what I would do would be to separate data in 2 files, create 2 temporary tables, one with PRIME_ID, A_SEQ_NO, STARTDATE, ENDDATE, and the other with the days instead of the dates, then you insert data in these 2 tables, and do an INSERT ... SELECT using NVLs to insert all your data in TIMES ...

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    try to make notice of the position
    of each fields. And specally if the field can be NULL
    the corresponding data can also be null. There might
    be an issue of overlapping.


  4. #4
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    well, not just an issue of overlapping.
    as what pipo says you have heterogeneous data.
    You can also use the bad files to be loaded
    again to another table. If there is necessary
    to merge two table then you have to transform
    data from one table to be uniform to another table.


  5. #5
    Join Date
    Mar 2001
    Posts
    46

    Question Is there another way of creating the loader file?

    Would a coma separated loader file have this problem, also?

    I've only worked with fixed position data so I've never setup a variable length control file.
    ora_newbie@yahoo.com

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    no, since a field with only dates would look like :

    1,2,some date, some other date,,

    and one with only days would look like :

    1,2,,,3,4

    no confusion is possible that way ...

  7. #7
    Join Date
    Mar 2001
    Posts
    46

    Another solution: sqlldr nullif

    In the interest of sharing solutions.

    I found another solution to the heterogenous data.
    Sqlldr is extremely powerful and I've found an option called NULLIF that will return a value of null if the condition is met so the contol file can be changed to this:

    STARTDATE position (5:23) date "yyyy-mm-dd hh24:mi:ss" NULLIF STARTDATE=blanks,

    ENDDATE position (25:43) date "yyyy-mm-dd hh24:mi:ss" NULLIF ENDDATE=blanks,

    Enjoy.
    ora_newbie@yahoo.com

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