Search notes:

SQL*Plus: Variables

There are three types of variables in SQL*Plus:
Variable type Declaration Displaying value
System variables set show
Substitution variables define prompt (with &)
Bind variables variable print

Bind variables

A bind variable is declared with SQL*Plus's variable statement.
The value of a bind variable can be changed within a PL/SQL block or a PL/SQL function or procedure:
VARIABLE num NUMBER

begin
  :num := 42;
end;
/

print num

select :num from dual;

Substitution variables

define num = 42
prompt &num
select object_name from user_objects where rownum <= &num;
The value for a substitution variable can be assigned from user input with accept.
The value of a column of a select statement can be assigned with COLUMN colname NEW_VALUE varname.
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.
Using variables can be turned of with set define off.
The special meaning of the ampersand can be suppressed using set escape.
Commands that relate to substitution variables include

System variables

set set serveroutput on;
…
show serveroutput
The value of a system variable can be shown with the show command:
SQL> show serveroutput

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 := &num;

See also

SQL*Plus

Index