|
6 Substitution Variable Namespace, Types,
Formats and Limits
6.1 Substitution Variable Namespace
In a SQL*Plus session there is just one global name
space for substitution variables. If you reconnect using CONNECT, or run
subscripts using "@", all variables ever defined are available for use and
may be overridden or undefined.
When a child script finishes, all substitution variables
it defined or changed are visible to the calling script. This is particularly
noticeable when a subscript executed with "@" or START is given script parameters. The parameters "&1" etc.
get redefined and the parent script sees the new values.
To minimize problems, and for general readability,
use symbolic variable names for command parameters. All other references
should use the new variable name instead of "&1". For example:
define myuser = '&1'
@myscript.sql King
select first_name from employees where last_name = '&myuser';
The call to myscript.sql changes the value of "&1" to "King".
By saving the original value of "&1" in "myuser" and using "&myuser" instead
of "&1" in the SELECT, the query executes correctly.
6.2 Substitution Variable Types
The substitution variable types stored by SQL*Plus
are:
The CHAR type is a generic text format similar to the
database table VARCHAR2 column type. All variables created by:
- DEFINE
- from prompts
for "&" variables
- from script parameters
are of type CHAR. This ensures that values entered
are substituted verbatim with no conversion loss.
Substitution variables of type NUMBER are stored in
Oracle's internal number representation. Variables created by COLUMN NEW_VALUE
or OLD_VALUE use the NUMBER type for numeric columns (and CHAR for all other
column types). Using Oracle's internal number representation allows number
display formats to be altered without any internal value loss after the variable
is created.
There is no explicit DATE type. The DATE keyword in
the ACCEPT command is used solely to allow correct format validation against
a date format. Substitution variables created by ACCEPT ... DATE, or by COLUMN
NEW_VALUE on a date column, are stored as type CHAR. For example:
SQL> accept mydvar date format 'DD-MON-YYYY' prompt 'Enter a date: '
Enter a date: 03-APR-2003
SQL> define mydvar
DEFINE MYDVAR = "03-APR-2003" (CHAR)
If a variable already exists and is redefined, its
old type is discarded and the new type used.
The type of a substitution variable is generally transparent.
Substitution variables are weakly typed. For example, a COLUMN NEW_VALUE
variable takes on the particular type of the named column in each new query.
It may also change type during a query. For example, the type of a substitution
variable used on a NUMBER column changes from NUMBER to CHAR when a NULL
value is fetched. It changes back to NUMBER when the next numeric value is
fetched.
No type comparison semantics are defined for any type
since there is no direct comparison of variables. All variables are textually
substituted before any SQL or PL/SQL statement that could do a comparison
is executed.
6.3 Substitution Variable Formats
When a variable is substituted, or its value is shown
by a DEFINE command, it is formatted as text before the command referencing
the variable is finally executed.
CHAR variables are substituted verbatim.
NUMBER variables are formatted according to SET NUMWIDTH
(by default) or SET NUMFORMAT (if you have explicitly set one):
The display format of a number can be changed even
after the variable is created. To show this, first create a NUMBER variable.
You cannot use DEFINE to do this because it makes the type of all new variables
CHAR. Instead use a COLUMN NEW_VALUE command which inherits the NUMBER type
from a NUMBER column:
SQL> column c2 new_val m
SQL> select 1.1 c2 from dual
C2
----------
1.1
SQL> define m
DEFINE M = 1.1 (NUMBER)
Changing the format affects the display of the number
but not the stored value:
SQL> set numformat 99.990
SQL> define m
DEFINE M = 1.100 (NUMBER)
For a list of format models, see Format
Models in the Oracle9i SQL Reference.
6.4 Substitution Variable Limits
The maximum number of substitution variables allowed
is 2048. SQL*Plus gives an error an attempt is made to create more. The limit
includes the predefined variables, however these can be undefined if necessary.
Leaving a large number of unnecessarily defined variables can reduce the
performance of SQL*Plus because variable lookups are slower.
A character substitution variable can be up to 240
bytes long.
A numeric substitution variable holds the full range
of Oracle numbers. See NUMBER
Datatype in the Oracle9i SQL Reference Release 2.
When a command line undergoes variable substitution,
the resulting line length can be no more than:
Otherwise an error is displayed.
These limits may be lower in old versions of SQL*Plus.
| 
Bookmark
