|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:
After the declaration, a value can be assigned to the variable
The value of the bind variable can then be printed with print:
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:
And then another record can be inserted:
exec insert into sqlplus_bindvar_ex values (:num_var, :txt_var)
select * from sqlplus_bindvar_ex
See also variable.