Problem using sqlldr with LONG datatype
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Problem using sqlldr with LONG datatype

  1. #1
    Join Date
    May 2001
    Location
    Jacksonville, FL
    Posts
    12
    I'm trying to use sqlldr to load a pipe-delimited flatfile into a table with a LONG datatype. That's not hard, except that the text to be loaded into the LONG column is full of cr/lf (they're resumes, and the data owner wants the formatting preserved).

    Control file looks like this:
    LOAD DATA
    INFILE 'MD029candidateinfo.txt'
    BADFILE 'MD029candidateinfo.bad'
    APPEND
    INTO TABLE candidates
    FIELDS TERMINATED BY "|"
    (
    candidateid,
    active,
    resumertf char(200000) ENCLOSED BY '"' AND '"')

    Datafile looks like this:
    12345|1|"Resume of Larry Ellison...^M
    resumetext....."^M
    67890|2|"Resume of Bill Gates...^M
    resumetext......"^M

    The '^M' characters are the way Unix displays the cr/lf values. I've tried substituting them with various other characters, however my log and bad files from SQLLDR indicate that SQLLDR thinks the '^M' character signals the start of the next record. Please help!

    Thanks,

    Joe

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    are you using Unix or NT ??

  3. #3
    Join Date
    May 2001
    Location
    Jacksonville, FL
    Posts
    12
    I'm using Compaq Tru 64 Unix.

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131

    Lightbulb

    I know that the LONG datatype defaults to 80 charachters unless otherwise specified.
    MH

  5. #5
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    first thing to do is to get rid of the ^M, you can either make your FTP again (since I guess you did a FTP between a NT and a Unix Box) using binary mode (simply type binary), or you can vi your datafile and do something like :
    :%s/^M//
    (to get ^M, you type Ctrl-V and then Enter)

  6. #6
    Join Date
    May 2001
    Location
    Jacksonville, FL
    Posts
    12

    Talking

    Thanks. For future reference in the forums, I removed the '^M' and added a '#\n' at the end of each record (the # was chosen arbitrarily, and could be any symbol combined with the '/n' which indicates a newline in Unix. Control file then looks like this:
    LOAD DATA
    INFILE 'MD029candidateinfo2.txt' "str '#\n'"
    BADFILE 'MD029candidateinfo2.bad'
    APPEND
    INTO TABLE candidates
    FIELDS TERMINATED BY "|"
    (
    candidateid INTEGER EXTERNAL,
    active INTEGER EXTERNAL,
    resumertf char(200000)ENCLOSED BY '"' AND '"')

    Each field is delimited by pipe, with resume text further enclosed in quotes.

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