René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Using bind variables in SQL*Plus

There are two types of variables that can be used in SQL*Plus: bind variables and user variables (which are declared with define. The following discusses bind variables.
In SQL*Plus, a bind variable is declared with variable:
var num_var number
var txt_var varchar2(15)
After the declaration, a value can be assigned to the variable
begin
  select 44, 'fourty-four' into :num_var, :txt_var from dual;
end;
/
The value of the bind variable can then be printed with print:
print num_var
using the variable:
create table sqlplus_bindvar_ex(
  num number, txt varchar2(15)
);

begin
  insert into sqlplus_bindvar_ex values (:num_var, :txt_var)
end;
/

Assigning a value to a bind variable with execute

Since an execute is basically a wrapper around a begin .. end PL/SQL block, a variable can be assigned a value like so:
exec :num_var := 42
exec :txt_var := 'fourty-two'
And then another record can be inserted:
exec insert into sqlplus_bindvar_ex values (:num_var, :txt_var)
select * from sqlplus_bindvar_ex

Links

See also variable.