Controlling Variable Substitution

As you use SQL*Plus, two problems may 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 common problem 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, is that you may want to place a substitution variable smack in the middle of a word. This is a less common problem.

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.

8.6.1 The Escape Character

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

DEFINE friends = "Joe & Matt"

If you executed that command, SQL*Plus would interpret " & Matt " as a substitution variable and would prompt you to supply a value. The result would look like this:

SQL> DEFINE friends = "Joe & Matt" Enter value for matt:

That's not the behavior you want, yet the ampersand is legitimately part of the string, 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 friends = "Joe & Matt"

However, the escape feature is not on by default. In order for this approach to work, you must enable the escape feature.

8.6.1.1 Enabling the escape feature

By default, SQL*Plus doesn't check for escape characters when looking for substitution variables. You must turn on this feature 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.

8.6.1.2 Escaping an ampersand

Now that the escape feature has been turned on, you can place a backslash in front of any ampersand characters you need to embed in your script. The following is a modified version of the previous example that correctly assigns the text " Joe & Matt " to the friends variable:

SQL> SET ESCAPE ON SQL> DEFINE friends = "Joe & Matt"

You can see the current value of the variable by issuing the DEFINE command followed by the variable name:

SQL> DEFINE friends DEFINE FRIENDS = "Joe & Matt" (CHAR)

Because of the preceding backslash, SQL*Plus leaves the ampersand alone, and the friends 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.

8.6.1.3 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.

8.6.2 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 a 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

8.6.2.1 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.

8.6.2.2 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 three ways to deal with this problem. One is to turn the concatenation feature off. Another 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 execute the example again, 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 turns the feature on.

You can also put two periods at the end of the line, one to end the substitution variable and one to end the sentence:

SQL> PROMPT This sentence has no &last_word..

So long as concatenation is enabled, only one period will actually display.

8.6.3 Enabling and Disabling Substitution

Sometimes it's easier to turn substitution completely off rather than worry about how you use ampersand and escape characters in your scripts. You can turn variable substitution completely off with this command:

SET DEFINE OFF

To reenable substitution, simply issue:

SET DEFINE ON

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 afterward:

. . . SET DEFINE OFF Toggle substitution off for the next few commands. . . . Portion of script that doesn't reference substitution variables goes here. . . . SET DEFINE ON Toggle substitution back on when needed again. . . .

8.6.4 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.

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 resets to the default. This is true regardless of whether substitution is currently on or off.