column <something2?> <something3?>
something1=alias you’ll use in query where you’ll assign value
something2=leave as is always “new_value” hardcoded!
something3=this is alias which is always called with ampersand i.e &real_value
Imagine this case scenario (emp table from scot schema)
SQL> select empno, ename, mgr from emp;
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7839 KING
7844 TURNER 7698
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
12 rows selected.
Now let’s make query with two user defined variable (something like your case)
– assign a_v1 as 7844.
col a_v1 new_value a_real_value1;
select 7844 a_v1 from dual;
– a_v1 passed value to &a_real_value1
– let us see the content of &a_real_value1
select &a_real_value1 from dual;
– this &a_real_value1 will be used later as part of where condition
– now let’s assign a_v2 variable
col a_v2 new_value a_real_value2;
select ename a_v2 from emp where empno=&a_real_value1;
– a_v2 is assigned with ename which is retrieved with empno 7844 (defined through &a_real_value1 variable)
– now let us see value of second variable
– it is string soit should have ampersand
select ‘&a_real_value2’ final_value from dual;
now here is whole example without comments:
SQL> select &a_real_value1 from dual;
old 1: select &a_real_value1 from dual
new 1: select 7844 from dual
** 7844**
----------
** 7844**
SQL> col a_v1 new_value a_real_value1;
SQL> select 7844 a_v1 from dual;
** A_V1**
----------
** 7844**
SQL> select &a_real_value1 from dual;
old 1: select &a_real_value1 from dual
new 1: select 7844 from dual
** 7844**
----------
** 7844**
SQL> col a_v2 new_value a_real_value2;
SQL> select ename a_v2 from emp where empno=&a_real_value1;
old 1: select ename a_v2 from emp where empno=&a_real_value1
new 1: select ename a_v2 from emp where empno= 7844
A_V2
----------
TURNER
SQL> select ‘&a_real_value2’ final_value from dual;
old 1: select ‘&a_real_value2’ final_value from dual
new 1: select ‘TURNER’ final_value from dual
FINAL_VALUE
--------------------------------
TURNER
SQL>
And this is exactly your ask how to emulate “select data into &&variable2 from database.table where field = ‘&&variable1’” in sqlplus.
