|
5 SQL*Plus Substitution Variable Commands
Substitution variables can be used to replace options
and values in almost all SQL*Plus commands. Several of the commands have
special significance for substitution variables. These are discussed below.
5.1 ACCEPT Command
The ACCEPT command always prompts for a variable's
value, creating a new variable or replacing an existing one. ACCEPT has advantages
over a double ampersand (&&) variable reference that causes a prompt.
ACCEPT allows the prompting text to be customized and allows a default value
to be specified. ACCEPT does type and format checking.
SQL> accept myv number default 10 prompt 'Enter a number: '
Enter a number: _
In this example, if you enter alphabetic characters
then an error is shown and you are re-prompted. If you press Enter without
typing anything then the variable takes the value "10".
The ACCEPT command understands numbers, strings and
dates. If a FORMAT clause is used, SQL*Plus validates the input against the
given format. If the input is not valid, you are re-prompted for a value.
For a list of format models, see Format
Models in the Oracle9i SQL Reference.
If a FORMAT specifier such as "A10" is used for a CHAR
variable, the entered value may be any length up to and including 10 characters.
If a FORMAT specifier is used for a NUMBER type, the
allowed inputs depend on the specifier used. For example, a specifier of "9EEEE" (for
exponential notation) allows "3e2" but not "300" or "12e2". A format specifier
of "999.99" allows both "123.45" and "67" to be entered.
DATE variables are validated against an explicitly
supplied FORMAT or against the default session date format (like "DD-MON-YYYY").
After successful validation against the format model,
variables are stored in the appropriate variable type. See 6.2 Substitution Variable Types. In particular,
substitution variables created with an ACCEPT ... DATE command are stored
with type CHAR.
The ACCEPT ... HIDE option can be used to prevent the
value you enter from being displayed on the screen. This can be useful for
scripts that need to prompt for passwords. Note some operating systems cannot
redirect batch program script output into an ACCEPT ... HIDE command.
See ACCEPT in
theSQL*Plus User's Guide and Reference for the full ACCEPT command
syntax.
5.2 COLUMN Command
The COLUMN NEW_VALUE and COLUMN OLD_VALUE commands
can be used to associate a substitution variable with a SELECT column's data.
SQL> column department_id new_value dnv
When column "department_id" selected, a substitution
variable "dnv" is created to hold each row of the column in turn. The variable
remains defined after the query completes:
SQL> select department_id
2 from departments
3 where department_id between 60 and 100;
DEPARTMENT_ID
-------------
60
70
80
90
100
SQL> define dnv
DEFINE DNV = 100 (NUMBER)
COLUMN OLD_VALUE and NEW_VALUE substitution variables
can be used for basic numeric and text column types. They are used for any
query executed that has a column of the same name as the COLUMN command.
Their current value can be displayed in report headings and titles during
query execution.
COLUMN NEW_VALUE variables should be used in TTITLE
and REPHEADER titles at start of a page. They hold data from the new row
about to be printed on the page. After the query finishes the variable has
value of the last row.
COLUMN OLD_VALUE variables are used in BTITLE and REPFOOTER
titles at the end of a page. They contain data from the old row most
recently printed on the page. See 5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands for
discussion and examples.
Variables change type as required. If another query
with the same column name is run, the variable may take on a new type. Also
if a number column contains null values, a substitution variable on the column
changes from type NUMBER to CHAR for that row. This lets it hold the current
string for the SET NULL option. The variable changes back to NUMBER when
the next numeric value is fetched.
If no rows are selected by a query, and the substitution
variable does not already exist, then a zero length CHAR variable is created.
If the substitution variable exists then its value and type are not changed.
Similar to the DATE option for the ACCEPT command, a
variable on a DATE column is stored as type CHAR.
COLUMN NEW_VALUE and OLD_VALUE can be used to transfer
a value from a bind variable to a substitution variable. See 3.2 Assigning Bind Variables to Substitution Variables.
The COLUMN option NOPRINT can be used to suppress query
results and stop them appearing in a final report.
See COLUMN in
the SQL*Plus User's Guide and Reference for the command syntax.
5.3 DEFINE Command
Use the DEFINE command to explicitly create substitution
variables:
SQL> define myv = 'King'
The DEFINE command can also be used to display the
value of a known variable. It shows the variable name, value and type:
SQL> define myv
DEFINE MYV = "King" (CHAR)
Using DEFINE with no arguments lists all defined substitution
variables. Any variable that DEFINE lists is said to be defined.
A variable may be redefined by repeating the DEFINE
command with a different value.
The DEFINE command only ever creates variables with
type CHAR.
See DEFINE in
the SQL*Plus User's Guide and Reference for the command syntax.
5.4 EDIT Command
The EDIT command starts an external editor such as
Notepad or Vi. On most operating systems SQL*Plus has a predefined substitution variable called _EDITOR
set to the default editor's executable:
SQL> define _editor
DEFINE _EDITOR = "Notepad" (CHAR)
EDIT can edit a named file. It can also edit the current
SQL buffer (which holds the most recently executed SQL statement). When the
external editor is closed the changed statement is loaded back into the SQL
buffer. EDIT writes the SQL buffer to a temporary file called afiedt.buf.
The temporary file name can be changed with the SET EDITFILE command.
You can redefine the value of the _EDITOR substitution
variable to any editor.
On Windows _EDITOR can be set to "write.exe" to invoke
WordPad. However, if the SQL buffer is being edited, the buffer is not automatically
updated with the modified script. This is because SQL*Plus cannot tell when
the WordPad editor has been closed.
A recommended way to create SQL*Plus scripts is to
explicitly specify a file name for EDIT and then use the START or "@" commands
to run this file.
See EDIT and SET
EDITFILE, both in the SQL*Plus User's Guide and Reference.
5.5 EXIT Command
On many operating systems the EXIT command can pass
the value of a numeric bind variable or substitution variable to the operating
system environment. On UNIX, the return status from SQL*Plus can be displayed
with the command "echo $?" in the Bourne, Korn and Bash shells, or with "echo
$status" in the C shell.
To return a substitution variable, it is recommended not to
use an ampersand prefix before its name. If you use "&" or "&&",
the command preprocessor does the substitution using default number formatting
rules (see 6.3 Substitution Variable Formats)
before the EXIT command is finally parsed and executed. This is normal pre-processing
of a numeric substitution variable in a command. Since the Oracle format
specifier may include decimal and group separators which are not digits,
or the number may overflow the format and be substituted as pound signs (#),
there may be problems doing the final conversion from the resulting formatted
string to the operating system return status. For example, if the formatting
rules return exponential format and the number is formatted as "4E+05", then
only the value "4" is returned to the operating system by the EXIT command.
When "&" does not prefix the substitution variable
name, e.g. "EXIT myv", SQL*Plus internally uses the more practical format
specifier "9999999990" to convert from the internal number format to the
string used as the EXIT command parameter.
Note some operating systems limit the number range
that can be returned from a program. On such systems the returned value may
overflow and contain an unexpected number. This commonly limits the use of
the system variable SQL.SQLCODE which contains the last
Oracle error number. Typically this number is larger than an operating system
supports as an exit return status.
If a non numeric variable is referenced in an EXIT
statement the EXIT command exits but reports an error. The operating system
return value is the same as for EXIT FAILURE.
Substitution variables are not saved when SQL*Plus
exits. Only the predefined substitution variables and any variables
set in the site and user profiles (e.g. glogin.sql and login.sql)
are defined when you next start SQL*Plus.
See EXIT in
the SQL*Plus User's Guide and Reference.
5.6 HOST Command
The HOST command runs a specified operating system
command or opens a command window. On some operating systems a character
like "!" or "$" is a synonym for HOST.
After a HOST command finishes then the substitution
variable _RC is defined. Its value is port specific and may contain a text
message. On UNIX it is defined as "0" if the command is successful, or "1" if
not. It may also be the operating exit status returned from the host program.
On other platforms the value of _RC is not well defined and its value should
not be relied on.
See HOST in
the SQL*Plus User's Guide and Reference.
5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER
Commands
Variables are used in report titles to make each page
relate to the data on that page, for example to give the product item that
the report page describes. Any substitution variable can be used in a title
command. However, the COLUMN NEW_VALUE or OLD_VALUE commands are often used
to associate variables with column values in a report query.
Use NEW_VALUE variables in TTITLE and REPHEADER commands.
Use OLD_VALUE variables in BTITLE and REPFOOTER commands. For example, the
script:
column last_name new_value ttnv old_value btov
ttitle left 'First employee is: ' ttnv
btitle left 'Last employee is: ' btov
select last_name from employees where department_id = 60 order by last_name;
gives the output:
First employee is: Austin
LAST_NAME
-------------------------
Austin
Ernst
Hunold
Lorentz
Pataballa
Last employee is: Pataballa
See TTITLE, BTITLE, REPHEADER,
and REPFOOTER in
the SQL*Plus User's Guide and Reference.
5.7.1 Using "&" Prefixes
With Title Variables
The title commands (TTITLE, BTITLE, REPHEADER and REPFOOTER)
substitute variables differently to most other commands. (The exceptions
are the EXIT and SET SQLPROMPT commands,
which are similar to the title commands). In general you do not need, and
will not want, to put an "&" prefix before a variable name in a title
command. For example, if your TTITLE command is:
ttitle left 'Urgent: ' &2 ' Days High: ' &days
you should possibly change it to:
ttitle left 'Urgent: ' 2 ' Days High: ' days
The guidelines for variables in titles are:
-
If you want the same value for a variable to be
printed on every page then use an "&" prefix and put the variable
inside a quoted string:
accept mycustomer char prompt 'Enter your company name: '
ttitle left 'Report generated for company &mycustomer'
select last_name, job_id from employees order by job_id;
-
If you want each title to have data from the query
that is unique to each report page then do not use an "&" prefix
for the variable and do not put the variable inside quotes.
column job_id new_value ji_nv noprint
break on job_id skip page
ttitle left 'Employees in job: ' ji_nv
select last_name, job_id from employees order by job_id;
SQL*Plus substitution variables are expanded before
each command is executed. After this happens in a title command, the resulting
string is stored as the title text. What makes variables in titles special
is that they need to be re-substituted for each page of query results. This
is so the current COLUMN NEW_VALUE and OLD_VALUE substitution variable values
are displayed on each page, customizing each title for the results displayed
on its page. If "&" is used inadvertently or incorrectly to prefix title
variables, it is possible to get double substitution. This is dependent on
the variable's value and is easily overlooked when you write scripts.
Any non-quoted, non-keyword in a title is checked when
the page is printed to see if it is a variable. If it is, its value is printed.
If not, then the word is printed verbatim. This means that if you use "&myvar" in
a title command, and the text substituted for it can itself be interpreted
as another variable name then you get double variable substitution. For example,
the script:
define myvar = scottsvar
ttitle left &myvar
define scottsvar = Hello
select * from dual;
causes the text "left scottsvar" to be stored as the
title. When the title is printed on each page of the query this string is
re-evaluated. The word "scottsvar" in the title is itself treated as a variable
reference and substituted. The query output is:
Hello
D
-
X
Using "&" in titles most commonly causes a problem
with the numeric variable names of the SQL*Plus script parameters. If the value of an arbitrary "&"-prefixed
title variable is the same as a script parameter variable name, then double
substitution will occur.
To display an "&" in a title, prefix it with the SET ESCAPE character. The ampersand (&) is
stored as the title text and is not substituted when page titles are printed.
5.7.2 Variables and Text Spacing
in Titles
Unquoted whitespace in titles is removed. Use whitespace
instead of the SET CONCAT character to separate variables from text that
should appear immediately adjacent. Use whitespace inside quotes to display
a space. For example, the script:
define myvar = 'ABC'
ttitle left myvar myvar Text ' Other words'
select ...;
gives a title of:
ABCABCText Other words
5.8 UNDEFINE Command
Use UNDEFINE to remove a defined substitution variable:
SQL> undefine myv
SQL> define myv
SP2-0135: symbol myv is UNDEFINED
Any variable not listed by DEFINE is said to be undefined.
Undefining unused substitution variables may help improve
SQL*Plus performance because SQL*Plus can look up variables faster. This
is especially true when variables are used in the SQLPROMPT.
See UNDEFINE in
the SQL*Plus User's Guide and Reference for the command syntax.
5.9 WHENEVER Command
Substitution variables used for return statuses in
WHENEVER OSERROR EXIT or WHENEVER SQLERROR EXIT commands follow the same
general guidelines as variables in EXIT commands.
Specifically, no ampersand (&) prefix is required, for example:
SQL> whenever sqlerror exit myv
Be careful of using an ampersand (&) prefix for
substitution variables in WHENEVER ... EXIT commands. Using an ampersand
causes the current value of the variable at the time the WHENEVER command
is run to be used, not the value that is in effect when the program later
exits. For example, in the script:
define myv = 5
whenever sqlerror exit &myv
define myv = 10
-- This query should fail
select * from non_existent_table;
the operating system return status is 5. This is because
the WHENEVER statement is pre-processed and executed as if you typed:
whenever sqlerror exit 5
However, if you remove the ampersand:
define myv = 5
whenever sqlerror exit myv
define myv = 10
-- This query should fail
select * from non_existent_table;
the return status is "10" which is the value of "myv" at
the time of exit.
See WHENEVER
OSERROR and WHENEVER
SQLERROR in the SQL*Plus User's Guide and Reference.
| 
Bookmark
