How to run SQL statements in SQL*Plus if you have ‘&’ as part of your data?

If you have ‘&’ as part of your data that is part of the string and needs to inserted/updated/deleted and you are using SQL*Plus, you would need to do the following as ‘&’ has a special meaning in SQL*Plus as by default SQL*Plus considers it as a variable.

E.g: SQL> select ‘xxxx &abc’ from dual;
In the example SQL*Plus will prompt to enter a value for variable abc

So to ensure ‘&’ is taken in it’s literal value and it doesn’t prompt, one can do one of the following if you need to run SQL scripts from SQL*Plus

1) SET DEFINE OFF — Turns off substitution of variables
2) SET DEFINE # — Change the substitution character to # or to a character that is not part of the script
3) Change the above sample SQL to the following
SQL> select ‘xxxx &’ || ‘abc’ from dual;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s