Substitution variables
define num = 42
prompt &num
select object_name from user_objects where rownum <= #
The value for a substitution variable can be assigned from user input with
accept
.
SQL*Plus limits the number of substitution variables to 2048.
The maximum length of a character substitution variable is 240 bytes.
Substitution variables (whose value contain ampersands) are not recursively expanded.
The special meaning of the ampersand can be suppressed using
set escape
.
Commands that relate to substitution variables include
Assigning substitution variables to bind variables
the following snippet demonstrates how the value of a substitution variable can be assigned to a bind variable. (
exec is just a wrapper for a one statement
PL/SQL block which allows for the assignment):
define num = 123
variable bnd number
exec :bnd := #