Oracle: Escape ampersand (&) characters in SQL*Plus

When importing a backup you might have run into the problem that the ampersands (&) or colon () characters are used as bind variables in PL/SQL. This is actually no real problem, as you can simply change the substitution character using the DEFINE setting in SQL*Plus:

The usual substitution mechanism an ampersands would trigger, is now suppressed.

Other methods:
Define an escape character:

Don’t scan for substitution variables:

Another way to escape the & would be to use concatenation, which would not require any SET commands –

Use the 10g Quoting mechanism:

You can also turn off substitutions by setting define to off:

When you need it, just turn it on again:


Related Posts

Eclipse: convert upper case text to lower case and viceversa with a simple shortcut


November 7, 2012

Development, Java

No Comment

This is just a quick post to share these 2 little shortcuts to convert text from lower case to uppercase and vice-versa in Eclipse. Lower case: CTRL+SHIFT+Y (CMD+SHIFT+Y on Mac OS X) Upper case: CTRL+SHIFT+X (CMD+SHIFT+X on Mac OS X) Camel case: Anyone knows a shortcut? You can select one or more characters to convert.

Read More

OpenERP: Connect Kettle to OpenERP

[1] [2] [3] [4] [5] [6]

Read More

Leave a Reply


  • An error has occurred, which probably means the feed is down. Try again later.