DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: SQL Loader

  1. #1
    Join Date
    Apr 2006
    Posts
    24

    SQL Loader

    ctrl file
    INTO TABLE UG_HISTORY
    APPEND fields TERMINATED BY "#" optionally ENCLOSED BY '"' TRAILING NULLCOLS
    (
    UPDATEDT DATE 'YYYY-MM-DD HH24:MI:SS',
    )

    --------
    dat file
    >"2006-03-16 13:48:23.0"#
    >"2006-03-16 13:50:37.0"#
    >"2006-03-16 14:26:44.0"#
    ---------
    LOG:
    Record 1: Rejected - Error on table UG_HISTORY, column UPDATEDT.
    ORA-01830: date format picture ends before converting entire input string
    Record 2: Rejected - Error on table UG_HISTORY, column UPDATEDT.
    ORA-01830: date format picture ends before converting entire input string

    Record 3: Rejected - Error on table UG_HISTORY, column UPDATEDT.
    ORA-01830: date format picture ends before converting entire input string

    I do not understand in what problemma. help please

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you have a .0 on the end of the data

  3. #3
    Join Date
    Apr 2006
    Posts
    24
    aga
    I understood, but whether it is possible to set in control file such type of date what understood .0 because a dat file is formed by the program

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by tmp
    aga
    I understood, but whether it is possible to set in control file such type of date what understood .0 because a dat file is formed by the program
    Can you correct the program?

    Tamil

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Try this...
    UPDATEDT DATE 'YYYY-MM-DD HH24:MI:SS.S',

    edit... nix that, it only works with to_char(). Is the .0 significant?
    Last edited by KenEwald; 07-13-2006 at 10:01 AM.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    Try timestamp instead of date

    UPDATEDT timestamp 'YYYY-MM-DD HH24:MI:SS.ff'

  7. #7
    Join Date
    Apr 2006
    Posts
    24
    to correct the program this a superfluous cycle is necessary time will go away...
    if UPDATEDT DATE 'YYYY-MM-DD HH24:MI:SS.S', then:
    ORA-01821: date format not recognized

    ------
    UPDATEDT timestamp 'YYYY-MM-DD HH24:MI:SS.ff'
    this is work=)))))) thank you)))

  8. #8
    Join Date
    Jun 2006
    Posts
    259
    Your welcome

  9. #9
    Join Date
    Apr 2006
    Posts
    24
    yet question!
    log:
    Record 713: Rejected - Error on table UG_HISTORY, column TXTFILE.
    second enclosure string not present
    ctl file:
    LOAD DATA

    INFILE 'UG_HISTORY.dat'

    INTO TABLE UG_HISTORY
    APPEND fields TERMINATED BY "#" optionally ENCLOSED BY '№' TRAILING NULLCOLS
    (
    ID
    TXTFILE CHAR(4000),
    UPDATEDT timestamp 'YYYY-MM-DD HH24:MI:SS.ff',
    )

    bad file:
    №7702№#№Toru and Naoko's college romance
    might have been perfectly simple and predictable.
    They might have been confronted
    with
    the ordinary issues of becoming
    young adults in a large foreign city.
    ---------
    but farther there must be a date, but SQL loader breaks((
    alike from after transition on new line...
    how to avoid it?

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    Your text file is missing the enclosure character...

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