Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: sqlldr end-of-line

    When I output data from Sybase using their Bulk Copy Protocol (bcp), I use the syntax '\n' (newline-carriage return line feed) for the end-of-line character. In hex mode, this appears as '0D 0A'.

    SQL*Loader doesn't like this, as it seems to prefer '0A' as the end of file marker. How can I get SQL*Loader to recognize the Sybase output format?

    Thanks
    -Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    A couple a "details"
    There can only be ONE (single) end-of-line CHARACTER; not TWO!
    New-line = 0A hex.
    carriage-return = 13 hex
    Simply put, you specify the value of the LAST character on the line/row for SQLLDR.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074

    ok

    I found an option on the Sybase side '\\r' which plants a single '0D' (Chr13) at the end of each line. SQL*Loader currently sees this as the last character in the last field on the record though, rather than an end-of-line character.

    How would I communicate to sqlldr that I want this character noticed as an end-of-line marker?

    I've seen non-typical end-of-line examples like:

    INFILE myfile.dat "STR 'ab'"

    INFILE myfile.dat "STR x'00410042'"

    but I'm not sure how to get teh hex equivalent of '0D;.
    -cf

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    I'd still like to know how to specify the '0D' as an end-of-record marker, but I was able to provide an explicit end-of-record marker in Sybase, '&@', and then specify that in the SQL*Loader script:

    load data
    infile "/nas/transport/shrzipst.bcp" "STR '&@'"
    badfile "/nas/transport/shrzipst.bad"
    discardfile "/nas/transport/shrzipst.dsc"
    into table SHRZIPST
    fields terminated by "~"
    (TK,
    CITY,
    COUNTY_NAME,
    STATE_NAME,
    ZIPCODE,
    COUNTY_CODE,
    STATE_CODE)


    -Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •