Simple example
In the following example, two tables are created (new_value_test_from and new_value_test_to).
One record is inserted into new_value_test_from.
This record's only field is selected (and by means of new_value) assigned to the varlable var.
Then, the value of this variable is inserted into the other table, new_value_test_to.
set feedback off
set verify off
create table new_value_test_from (
col Varchar2(20)
);
create table new_value_test_to (
col Varchar2(20)
);
insert into new_value_test_from values ('Foo Bar Baz');
column col new_value var
select col from new_value_test_from;
prompt var: &var;
/*
var: Foo Bar Baz*
*/
insert into new_value_test_to values ('&var');
select * from new_value_test_to;
/*
COL
--------------------
Foo Bar Baz
*/
drop table new_value_test_from purge;
drop table new_value_test_to purge;
Multi-record example
If there are more than one rows in the record set, the variable is assigned with the value of the last record:
set feedback off
set verify off
create table new_value_test (
col Varchar2(20)
);
insert into new_value_test values ('val one' );
insert into new_value_test values ('val two' );
insert into new_value_test values ('val three');
column col new_value var
select col from new_value_test;
drop table new_value_test purge;
prompt var is: &var
/*
var is: val three
*/