Oracle SQL*Plus: The Definitive Guide

Controlling Variable Substitution

As you use SQL*Plus, two problems will eventually arise concerning the use of substitution variables. The first problem you are likely to encounter is that you will need to use an ampersand somewhere in your script, and you won’t mean for it to be part of a substitution variable name. This is a very common problem, and happens most often when you’re using an ampersand in a quoted string or as part of a comment.

The second problem, which you may never encounter at all, is that you may want to place a substitution variable smack in the middle of a word. This is a less common problem, and may not be an issue at all depending on the types of scripts you write.

SQL*Plus provides several ways to deal with these problems. A special escape character can be used whenever you need to place an ampersand in your script and have it stay there. A concatenation character is provided for those unusual cases where you want to place a substitution variable at the beginning or middle of a word. You can change the substitution character entirely if you don’t like using the ampersand and want to use some other character instead. Finally, if you aren’t really into writing scripts, you can turn the substitution feature completely off. Then you won’t have to worry about it at all.

The Escape Character

The escape character preceding an ampersand tells SQL*Plus to leave it alone — that it is not part of a substitution variable. Consider the following DEFINE command:

DEFINE company_name = "O'Reilly & Associates"

If you tried to execute that command, SQL*Plus would interpret “& Associates” as a substitution variable, and would prompt you to supply a value. The result would look like this:

SQL> DEFINE company_name = "O'Reilly & Associates"
Enter value for associates:

That’s obviously not the behavior you want, yet the ampersand is legitimately part of the name, so what do you do? One solution is to precede the ampersand character with a backslash, which is the default SQL*Plus escape character, like this:

DEFINE company_name = "O'Reilly \& Associates"

The escape feature is not on by default. In order for this to work, you need to enable it first.

Turning on the escape feature

By default, SQL*Plus does not check for escape characters when looking for substitution variables. This is a feature you must turn on before you use it. The command to do that is:

SET ESCAPE ON

Once turned on, this setting remains in effect until you turn it off again, or until you exit SQL*Plus.

Escaping an ampersand

Now that the escape feature has been turned on, you can place a backslash in front of any ampersand characters that you need to embed in your script. The following is a modified version of the previous example that correctly assigns the text “O’Reilly & Associates” to the company_name variable:

SQL> DEFINE company_name = "O'Reilly \& Associates"
SQL> DEFINE company_name
DEFINE COMPANY_NAME    = "O'Reilly & Associates" (CHAR)

Because of the preceding backslash, SQL*Plus leaves the ampersand alone, and the company_name variable is created containing the desired text.

One thing to keep in mind when you have the escape feature turned on is that you must escape the escape character itself when you need to use it as part of your script. For example, to define a string containing one backslash, you must double the backslash character as shown in the following code:

SQL> DEFINE backslash = "\\"
SQL> DEFINE backslash
DEFINE BACKSLASH       = "\" (CHAR)

If you are using the backslash a lot, and this causes you problems or becomes cumbersome, you can change the escape character to something else.

Changing the escape character

If you don’t like using the backslash as the escape character, you can use the SET ESCAPE command to specify a different character more to your liking. The following command changes the escape character to be a forward slash:

SET ESCAPE /

Changing the escape character also turns the escape feature on. There is no need to subsequently issue a SET ESCAPE ON command.

Tip

Any time you issue the SET ESCAPE ON command, the escape character is reset to the default backslash. This is true even if the escape feature was on to begin with. Keep this in mind whenever you issue SET ESCAPE ON, especially if you are using an escape character other than the default.

The Concatenation Character

There may come a time when you want to use a substitution variable in a situation where the end of the variable name is not clear. Consider the following code example:

DEFINE sql_type = "PL/"
PROMPT &sql_typeSQL

The intent is to have SQL*Plus print the text “PL/SQL, but SQL*Plus won’t substitute “PL/” in place of “&sql_type”. Instead, it will interpret the entire string of “&sql_typeSQL” as a variable.

You can get around this problem by using the SQL*Plus concatenation character. The period is the default concatenation character, and it explicitly tells SQL*Plus where the variable name ends. The following code example shows the concatenation character being used to make the substitution work as intended:

SQL> DEFINE sql_type = "PL/"
SQL> PROMPT &sql_type.SQL
PL/SQL

Turning off the concatenation feature

By default, the concatenation feature is always on. SQL*Plus looks for the period immediately following any substitution variables encountered in the script. If you need to, you can turn this feature off with the following command:

SET CONCAT OFF

It’s usually not necessary to turn this feature off. You would only need to do it if you were using periods after your substitution variables and you didn’t want those periods to disappear from your script. With concatenation on, any period immediately following a variable is used to mark the end of the variable name, and is removed from the script when the variable substitution is made.

Changing the concatenation character

The default concatenation character can cause a problem if you intend to use a substitution variable at the end of a sentence. The problem is that the period at the end of the sentence will go away because SQL*Plus sees it as the concatenation character ending the variable name. Here’s an example:

SQL> DEFINE last_word = 'period'
SQL> PROMPT This sentence has no &last_word.
This sentence has no period

There are only two ways to deal with this problem. One is to turn the concatenation feature off. The other is to change it to something other than a period. The following command changes the concatenation character to an exclamation point:

SET CONCAT !

Now you can reexecute the example, and the period at the end of the sentence shows up as expected:

SQL> DEFINE last_word = 'period'
SQL> PROMPT This sentence has no &last_word.
This sentence has no period.

As with the SET ESCAPE command, using SET CONCAT to change the concatenation character also turns the feature on.

Warning

Any time you issue the SET CONCAT ON command, the concatenation character is reset to the default period. This is true even if the concatenation feature was on to begin with. Keep this in mind whenever you issue SET CONCAT ON, especially if you are using a concatenation character other than the default.

Enabling and Disabling Substitution

You can turn variable substitution completely off with this command:

SET DEFINE OFF

Sometimes it’s easier just to turn substitution completely off rather than worry about how you use ampersand and escape characters in your scripts. If you have a large block of script that doesn’t reference any variables, you can toggle substitution off just for that block, and turn it on again afterwards. For example:

...
SET DEFINE OFF				Toggle substitution off for the next few commands

...
Script that doesn't reference substitution
variables goes here.
...
SET DEFINE ON				Toggle substitution back on when needed again

...

To reenable substitution, simply issue:

SET DEFINE ON

Changing the Substitution Variable Prefix Character

If you don’t like prefixing your substitution variables with an ampersand, or if you need to use ampersands in your script, you can tell SQL*Plus to use a different character for substitution. You can pick any character you like, but it should be something that stands out.

Changing the substitution variable prefix character

The following command changes the substitution variable prefix character to a caret:

SET DEFINE "^"

Changing the substitution character can be a handy thing to do if you need to use ampersands in a lot of text constants or if, like me, you tend to use them often in comments. The following code illustrates how to change from an ampersand to a caret and back again:

SQL> DEFINE message = "Brighten the corner where you are."
SQL> SET DEFINE ^
SQL> PROMPT &message
&message
SQL> PROMPT ^message
Brighten the corner where you are.
SQL> SET DEFINE &
SQL> PROMPT &message
Brighten the corner where you are.
SQL> PROMPT ^message
^message

Another way to reset the substitution character back to the default ampersand is to issue the SET DEFINE ON command. A side effect of issuing SET DEFINE ON is that the substitution character is always reset to the default. This is true regardless of whether substitution is currently on or off.