|
2 Using Substitution Variables
2.1 Creating, Showing and Deleting
Substitution Variables
Substitution variables can be explicitly created with
the DEFINE command. Defining a variable means storing a value for future
use:
SQL> define myv = 'King'
This creates a variable called "myv" containing the
text "King".
Another way to create substitution variables is with
the ACCEPT command. This can be used to prompt for a value:
SQL> accept myv2 char prompt 'Enter a last name: '
This command causes SQL*Plus to stop and prompt you
to enter a character string:
Enter a last name: _
What you enter is stored in the variable "myv2".
The DEFINE command can also be used to display known
variables. It shows the variable name, value and type. Any variable that
DEFINE lists is said to be defined:
SQL> define myv
DEFINE MYV = "King" (CHAR)
All variables that are currently defined can be shown
by executing the DEFINE command with no arguments:
SQL> define
DEFINE MYV = "King" (CHAR)
DEFINE MYV2 = "Taylor" (CHAR)
...
Any variable not listed is undefined:
SQL> define abc
SP2-0135: symbol abc is UNDEFINED
Substitution variables can be removed with the UNDEFINE
command:
SQL> undefine myv
2.2 Referencing Substitution Variables
Variables can be referenced by prefixing their name
with an ampersand (&):
SQL> define myv = 'King'
SQL> select employee_id from employees where last_name = '&myv';
SQL*Plus lists the statement line number and line containing
the substitution variable "myv" before and after substitution:
old 1: select employee_id from employees where last_name = '&myname'
new 1: select employee_id from employees where last_name = 'King'
Lines verifying substitution are displayed for SQL
or PL/SQL statements. The lines can be hidden with SET VERIFY OFF. Verification
never occurs for variables in SQL*Plus commands (e.g. SPOOL and SET).
The "new" line of the verification shows the query
executes as if you originally entered:
SQL> select employee_id from employees where last_name = 'King';
A more practical use of substitution variables is to
prompt for a value before referencing the variable:
SQL> accept myv char prompt 'Enter a last name: '
SQL> select employee_id from employees where last_name = '&myv';
If these two commands are stored in a SQL*Plus script,
a different last name can be entered each time the script is run.
2.3 Prompting for Undefined Variables
If a variable is referenced using an "&" prefix,
but the variable value is not yet defined, SQL*Plus prompts you for a value:
SQL> define myname
SP2-0135: symbol myname is UNDEFINED
SQL> select employee_id from employees where last_name = '&myname';
Enter value for myname:
After you enter a value, SQL*Plus substitutes the variable
and executes the query.
The Oracle Globalization Language setting (e.g. the
language component of the NLS_LANG environment variable) determines the exact
language used for the "Enter value for" prompt. The prompt text cannot otherwise
be changed.
2.4 Difference Between "&" and "&&" Prefixes
Both single ampersand (&) and double ampersand
(&&) can prefix a substitution variable name in a statement. SQL*Plus
pre-processes the statement and substitutes the variable's value. The statement
is then executed. If the variable was not previously defined then SQL*Plus
prompts you for a value before doing the substitution.
If a single ampersand prefix is used with an undefined
variable, the value you enter at the prompt is not stored. Immediately after
the value is substituted in the statement the value is discarded and the
variable remains undefined. If the variable is referenced twice, even in
the same command, then you are prompted twice. Different values can be entered
at each prompt:
SQL> prompt Querying table &mytable
Enter value for mytable: employees
Querying table employees
SQL> select employee_id from &mytable where last_name = 'Jones;
Enter value for mytable: employees
EMPLOYEE_ID
-----------
195
If a double ampersand reference causes SQL*Plus to
prompt you for a value, then SQL*Plus defines the variable as that value.
Any subsequent reference to the variable (even in the same command) using
either "&" or "&&" substitutes the newly defined value. SQL*Plus
will not prompt you again:
SQL> prompt Querying table &&mytable
Enter value for mytable: employees
Querying table employees
SQL> select employee_id from &mytable where last_name = 'Jones;
EMPLOYEE_ID
-----------
195
2.5 Storing a Query Column Value in
a Substitution Variable
Data stored in the database can be put into substitution
variables:
SQL> column last_name new_value mynv
SQL> select last_name from employees where employee_id = 100;
The NEW_VALUE option in the COLUMN command implicitly
creates a substitution variable called "mynv". When the SELECT finishes,
the variable "mynv" holds the last retrieved value from column "last_name":
SQL> define mynv
DEFINE mynv = "King" (CHAR)
2.6 Predefined Substitution Variables
The predefined substitution variables created when
you start SQL*Plus can be seen by entering DEFINE with no arguments. Each
predefined variable is prefixed with an underscore. The predefined variables
can be undefined or redefined just like user defined substitution variables.
In SQL*Plus Release 10.1 the predefined variables
are:
_CONNECT_IDENTIFIER
_DATE
_EDITOR
_O_RELEASE
_O_VERSION
_PRIVILEGE
_SQLPLUS_RELEASE
_USER
The variables _DATE, _PRIVILEGE, and _USER were introduced
in SQL*Plus 10.1. The variable _CONNECT_IDENTIFIER was introduced in SQL*Plus
9.2.
The variable _CONNECT_IDENTIFIER contains the connection
identifier used to start SQL*Plus. For example, if the SQL*Plus connection
string is "hr/my_password@MYSID" then the variable contains MYSID.
If you use a complete Oracle Net connection string like "hr/my_password@(DESCRIPTION=(ADDRESS_LIST=...(SERVICE_NAME=MYSID.MYDOMAIN)))" then
_CONNECT_IDENTIFIER will be set to MYSID. If the connect identifier is not
explicitly specified then _CONNECT_IDENTIFIER contains the default connect
identifier Oracle uses for connection. For example, on UNIX it will contain
the value in the environment variable TWO_TASK or ORACLE_SID. If SQL*Plus
is not connected then the variable is defined as an empty string.
The variable _DATE can be either dynamic, showing the
current date or it can be set to a fixed string. The date is formatted using
the value of NLS_DATE_FORMAT and
may show time information. By default a DEFINE or dereference using &_DATE
will give the date at the time of use. _DATE can be UNDEFINED, or set to
a fixed string with an explicit DEFINE command. Dynamic date behavior is
re-enabled by defining _DATE to an empty string.
The variable _EDITOR contains the external text editor
executable name. See 5.4 EDIT Command.
The variable _O_RELEASE contains contains a string
representation of the Oracle database version number. If your Oracle database
version is 9.2.0.3.0 then the variable contains "902000300". The Oracle version
may be different from the SQL*Plus version if you use Oracle Net to connect
to a remote database.
The variable _O_VERSION contains a text string showing
the database version and available options.
When SQL*Plus is connected as a privileged user the
variable _PRIVILEGE contains the connection privilege "AS SYSBDA" or "AS
SYSOPER". If SQL*Plus is connected as a normal user the variable is defined
as an empty string.
The variable _SQLPLUS_RELEASE contains the SQL*Plus
version number in a similar format to _O_RELEASE.
The variable _USER contains the current username given
by SHOW USER. If SQL*Plus is not connected, the variable is defined as an
empty string.
2.7 Script Parameters
Parameters can be passed to SQL*Plus scripts. For example,
from the command line:
sqlplus hr/my_password @myscript.sql King
You can also pass parameters when calling a SQL*Plus
script from within a SQL*Plus session, for example:
SQL> @myscript.sql King
Script parameters become defined substitution variables.
The variable name for the first parameter is "1", the second is "2", etc.
The effect is as if you start SQL*Plus and type:
SQL> define 1 = King
SQL> @myscript.sql
Commands in myscript.sql can reference "&1" to
get the value "King". A DEFINE command shows the parameter variable:
SQL> define 1
DEFINE 1 = "King" (CHAR)
Script parameter variables have type CHAR, similar
to variables explicitly created with DEFINE.
Quoting parameters with single or double quotes is
allowed. This lets whitespace be used within parameters. Operating systems
and scripting languages that call SQL*Plus handle quotes in different ways.
They may or may not pass quotes to the SQL*Plus executable. For example,
in a standard Bourne shell on UNIX, quotes around parameters are stripped
before the parameters are passed to SQL*Plus, and SQL*Plus never sees the
quotes.
It is recommended to check how quoted parameters are
handled on your operating system with your patch level of SQL*Plus. For portability
between UNIX and Windows environments use double quotes around parameters
containing whitespace.
SQL*Plus Releases 8.1.7, 9.2.0.3 (and other 9.x versions
patched for bug 2471872) and 10.1 onwards remove an outer set of single or
double quotes from parameters passed on the SQL*Plus command line. This makes
SQL*Plus behave the same way on operating systems that do not themselves
strip quotes as it does when the operating system strips the quotes before
calling SQL*Plus.
As an example of passing parameters, when SQL*Plus
10.1 is called in the UNIX shell script:
#! /bin/sh
sqlplus hr/my_password @myscript.sql "Jack and Jill"
only one program parameter is defined. References in myscript.sql to "&1" are
replaced with "Jack and Jill" (without quotes - because the shell script
does not pass quotes to SQL*Plus).
From SQL*Plus Release 9.0 onwards, an empty string
can be passed as a parameter.
2.8 More on Substitution Variables
Substitution variable references are pre-processed
and substituted before the command is otherwise parsed and executed.
For each statement SQL*Plus will:
1. Loop for each "&" and "&&" variable reference
If the variable is defined
Replace the variable reference with the value
else
Prompt for a value
Replace the variable reference with the value
If the variable is prefixed with "&&" then define
(i.e. store) the variable for future use
2. Execute the statement
Step 1 happens inside the SQL*Plus client tool. SQL*Plus
then sends the final statement to the database engine where step 2 occurs.
It is not possible to repeatedly prompt in a PL/SQL
loop. This example prompts once and the entered value is substituted in the
script text. The resulting script is then sent to the database engine for
execution. The same entered value is stored five times in the table:
begin
for i in 1 .. 5 loop
insert into mytable values (&myv);
end loop;
end;
/
Substitution variables are not recursively expanded.
If the value of a referenced variable contains an ampersand, then the ampersand
is used literally and is not treated as a second variable prefix:
SQL> set escape \
SQL> define myv = \&mytext
SQL> prompt &myv
&mytext
You cannot use a substitution variable as the first
token of a command. Each command name must be hard coded text otherwise an
error is displayed. For example:
SQL> &myv * from dual;
SP2-0734: unknown command beginning "&myv * fro..." - rest of line ignored.
Substitution variables cannot be used in buffer editing
commands like APPEND, CHANGE, DEL, and INPUT. Ampersands (&) in these
commands are treated literally.
If you wish to use alphanumeric characters immediately
after a substitution variable name, put the value of SET CONCAT - by default
a period (.) - to separate the variable name from the following characters.
For example, if "mycity" is defined as "Melbourne" then:
SQL> spool &mycity.Australia.log
is the same as:
SQL> spool MelbourneAustralia.log
If you want to append a period immediately after a
substitution variable name then use two periods together. For example, if "myfile" is
defined as "reports" then the command:
SQL> spool &myfile..log
is the same as:
SQL> spool reports.log
Text in ANSI "/* */" or "--" comments that looks like
a substitution variable may be treated as one, for example:
SQL> select department_id, location_id /* get dept & loc */ from departments;
Enter value for loc: _
Here the text "& loc" in the comment is interpreted
as a variable reference. SQL*Plus prompts you for a value for the variable "loc".
| 
Bookmark
