Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Escape ampersand in PL/SQL Developer

Tags:

I'm trying a simple INSERT statement against an Oracle database. One of the values is a VARCHAR2 field and the insert statement contains an ampersand. How do I do this? I've tried the following methods:

  1. Escape the & as \& with set escape on
  2. set scan off (this causes an ORA-00922 missing or invalid option error)
  3. set define off (this causes an ORA-00922 missing or invalid option error)

Any other ideas?

like image 507
Ray Booysen Avatar asked Aug 18 '11 07:08

Ray Booysen


People also ask

How do you escape a special character in PL SQL?

Use braces to escape a string of characters or symbols. Everything within a set of braces in considered part of the escape sequence. When you use braces to escape a single character, the escaped character becomes a separate token in the query. Use the backslash character to escape a single character or symbol.

How do you handle ampersand in SQL?

Use an ampersand (&) to identify each variable in your SQL statement. You do not need to define the value of each variable. Toggling the display of the text of a command before and after SQL*Plus replaces substitution variabfes with values.


1 Answers

At the bottom of a SQL window there is a button to disable/enable substitution variables:

enter image description here

like image 55
William Robertson Avatar answered Sep 30 '22 19:09

William Robertson