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.

Unfortunately, the values of substitution variables are not local to the script in which they occur (and even the SQLcl command argument does not improve matters by much).

Commands that relate to substitution variables include

accept
column
define, undefine
edit
exit
host
whenever
ttitle, btitle, repheader and repfooter.

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;

Variable type	Declaration	Displaying value
System variables	`set …`	`show`
Substitution variables	`define …`	`prompt` (with `&`)
Bind variables	`variable …`	`print`

SQL*Plus: Variables

Bind variables

Substitution variables

System variables

Assigning substitution variables to bind variables

See also