Date format - SQL Loader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Date format - SQL Loader

Hybrid View

  1. #1
    Join Date
    Apr 2002
    Posts
    50
    Hi all, I am using SQL Loader to transfer data from a text file (variable length, tab delimited) onto a table. I have a date field in the table and the corresponding value in the text file is like "2002-04-01 00:00:00".
    This is the format in my control file :

    (Resrv_date DATE "YYYY-MM-DD HH24:MM:SS")

    But, I get the error :
    Record1 - Rejected - Error on table xxx, column RESRV_DATE. Field in data file exceeds maximum length.

    But when I exp/imp the data or use SQL's DTS, all works fine. Any help is greatly appreciated.

    Thanks,
    Shiva.


  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    u have little error in date format:

    Resrv_date DATE "YYYY-MM-DD HH24:MM:SS"
    should be
    Resrv_date DATE "YYYY-MM-DD HH24:MI:SS"

    May be it help.

  3. #3
    Join Date
    Apr 2002
    Posts
    50
    Thanks for the suggestion, but it still get the same error.

    Shiva.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Try:

    Resrv_date DATE to_date(:Resrv_date,"YYYY-MM-DD HH24:MM:SS")

  5. #5
    Join Date
    Apr 2002
    Posts
    50
    Here is my control file :
    LOAD DATA
    INFILE '1.txt'
    INSERT INTO TABLE test_bam_util
    TRUNCATE
    REENABLE DISABLED_CONSTRAINTS
    FIELDS TERMINATED BY '\t'
    TRAILING NULLCOLS
    (resrv_date DATE "YYYY-MM-DD HH24:MM:SS",
    router CHAR,
    if_name CHAR,
    dummy1 FILLER,
    dummy2 FILLER,
    inbitssec CHAR)

    If I use FIELDS TERMINATED BY WHITESPACE instead of '\t', I am able to get the data into the table, but the date data "2002-04-01 00:00:00" gets spilt into 2 seperate fields - "2002-04-01" and "00:00:00" - because of the space and hence in my table, the values I have are like :
    resrv_date = "2002-04-01 00:00:00"
    router = "00:00:00"

    When I used the TO_DATE() functionality here, as in
    resrv_date DATE "TO_DATE(:resrv_date,'YYYY-MM-DD HH24:MI:SS')", this is the error I get :
    ORA-01821: date format not recognized

    Any ideas/suggestions ??

    Thanks,
    Shiva.

    [Edited by Shiva on 05-08-2002 at 12:56 PM]

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Some code for impuve ur script:
    ...
    FIELDS TERMINATED BY '\t' OPTIONALY ENCLOSED '"'
    TRAILING NULLCOLS
    ...
    (
    resrv_date DATE to_date(:resrv_date||' '||:router, 'YYYY-MM-DD H24:MM:SS'),
    ...

    in to_date u should use ' (not")

  7. #7
    Join Date
    Apr 2002
    Posts
    50

    Thumbs up

    Hi Shestakov, I used

    FIELDS TERMINATED BY WHITESPACE
    TRAILING NULLCOLS
    (entry DATE "YYYY-MM-DD HH24:MI:SS",
    router position(25:39) CHAR terminated by whitespace,
    if_name CHAR,
    dummy1 FILLER,
    dummy2 FILLER,
    inbitssec CHAR)

    and it works great.

    I could never get the to_date() function to work though, it would be nice to know why it did not, but as long as I get it to work, I am fine.
    Thanks for your continued help.

    Shiva.

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