Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003

    Unanswered: Simple noob question

    Have Excel macro creating SQL insert statements that are run in SQL*Plus environment. Problems arise when the values being inserted contain the '&' character.

    Can anyone provide me a refresher on using escape character for Oracle?

  2. #2
    Join Date
    Jul 2003
    in sql*plus issue:
    set define off

    or, goto $ORACLE_HOME/sqlplus/admin/glogin.sql
    and add the above line to that file.
    This will pre-setup everything for you each time you log in.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2004
    You can also escape characters like that in your queries :
    Instead of 'word1&word2', write 'word1'||chr(38)||'word2', where
    || means "concatenate" and 38 is the ASCII code of the '&' character.

  4. #4
    Join Date
    Jul 2003
    RBARAER and The_Duck, thank you both very much for your replies.

    Have tested both recommendations and both work just fine.

    Since I am not sure what other rammifications there may be to using 'set define off' in SQL*Plus, I think I will utilize VBA's Replace() function in the macro to substitute ||Char(38)|| for the & character.

    Thanks again for your help.

Posting Permissions

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