new_value in sql plus

new_value is a keyword in oracle sql plus that selects value to the user variable. This is mostly useful in sql scripting.
Here we see, the basic steps how to use it


A sample source table which holds some data
srikar@ORCL> create table t_test_from(a number);
srikar@ORCL> insert into t_test_from values(2);
srikar@ORCL> commit;

A sample table which holds the data that we insert
srikar@ORCL> create table t_test_to(b number);

Here is the syntax <>, the column_name value is selected to the user_variable
srikar@ORCL> column a new_value v_a
srikar@ORCL> select a from t_test_from;

A
----------
2

srikar@ORCL> insert into t_test_to values (&v_a);
srikar@ORCL> commit;
srikar@ORCL> select * from t_test_to;

B
----------
2

If the above query -- "select a from t_test_from" returns more than one rows, then our variable (user_variable) holds the last value

It needs to be included in single quotes for strings ('&user_variable')

srikar@ORCL> alter table t_test_from add (name varchar2(20));
srikar@ORCL> insert into t_test_from values (20,'Here we go ');
srikar@ORCL> commit;
srikar@ORCL> column name new_value v_name
srikar@ORCL> select name from t_test_from;

NAME
--------------------

Here we go
srikar@ORCL> alter table t_test_to add (name varchar2(20));
srikar@ORCL> insert into t_test_to(name) values('&v_name');
srikar@ORCL> commit;
srikar@ORCL> select * from t_test_to;

B NAME
---------- --------------------
Here we go


Here is the example --practical script that Tom Kyte has written (Read the below to know what does the script will do)
----------------------------------------------------------------------------------------
A DBA frequently needs to become another user to test something or verify a problem. Short of having to gain acess to that users password, we are asked can I su to that account, sort of like root does on unix.

This is an 'su.sql' script:


whenever sqlerror exit
column password new_value pw

declare
l_passwd varchar2(45);
begin
select password into l_passwd
from sys.dba_users
where username = upper('&1');
end;
/

select password
from sys.dba_users
where username = upper( '&1' )
/

alter user &1 identified by Hello;
connect &1/hello
alter user &1 identified by values '&pw';
show user
whenever sqlerror continue

it starts by testing your access to the sys.dba_users table -- if that fails -- it exits SQLPlus. If zero rows returned -- it exits SQLPlus.

It then selects the 'password' from the dba_users table and stuffs it into a macro variable "&pw"

We alter the user you want to become to have a known password (if that fails, we exit).
--------------------------------------------------------------------------------------

Thats all about it.