Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

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

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.

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.