Problem translating dbl quotes with sql loader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Problem translating dbl quotes with sql loader

  1. #1
    Join Date
    Oct 2007
    Posts
    4

    Problem translating dbl quotes with sql loader

    I have a data file that has intermittent double quotation marks in it that I want to translate to something else, like a "-". I haver used the sqlldr and the TRANSLATE command to convert other values, but sqlldr seems to problem with the ".

    Here is the command that I am using

    WELL_NAME "translate(:WELL_NAME,'"','-')",

    This is the error:

    SQL*Loader-350 Syntax error at line 25.
    Expecting ",' or ")", found ",".
    WELL_NAME "translate(:WELL_NAME,'"','-')",

    I have tried escaping the " with a \, but that did not work either.

    Can anyone help?

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Talking


    Try REPLACE instead:
    Code:
    WELL_NAME "REPLACE(:WELL_NAME,'"','-')",


    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Oct 2007
    Posts
    4
    REPLACE gets same results as TRANSLATE. It appears the problem is that as sqlldr parses the control file, it encounters the embedded double quotes, which assumes to be the end of the command string. Since our system apparently does not honor the \ as an escape char, is there some other way to embed the '"' in the command?

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Talking


    Use CHR() conversion:

    Code:
    WELL_NAME "REPLACE(:WELL_NAME,CHR(34),'-')",

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Oct 2007
    Posts
    4
    Good idea! That should work. I Hadn't thought of that. I'll give it a try next week when I am back in the office.

    Thank you

  6. #6
    Join Date
    Oct 2007
    Posts
    4

    Thumbs up

    Finally had a chance to try your suggestion: worked like a charm! Thank you.

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