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

new_value in SQL*Plus

The following construct can be used to select a value into a user variable:
column column_name new_value var_name
This is demonstrated 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 some_var_name.
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 (
  some_field_name Varchar2(20)
);

create table new_value_test_to (
  another_field Varchar2(20)
);

insert into new_value_test_from values ('Maegerli Mucki');

column some_field_name new_value some_var_name

select some_field_name from new_value_test_from;

insert into new_value_test_to values ('&some_var_name');

select * from new_value_test_to;

drop table new_value_test_from;
drop table new_value_test_to;
If there are more than one rows in the table, the last will be assigned to the variable.
set feedback off
set verify off

create table new_value_test (
  some_field_name Varchar2(20)
);


insert into new_value_test values ('Maegerli Mucki');
insert into new_value_test values ('Globi');

column some_field_name new_value some_var_name

select some_field_name from new_value_test;

drop table new_value_test;

prompt "The variable is: &some_var_name"