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.