Substitution Variables
Substitution Variables
- Use iSQL*Plus substitution variables to:
– Temporarily store values with single-ampersand (&) and double-ampersand (&&) substitution
- Use substitution variables to supplement the following:
– WHERE conditions
– ORDER BY clauses
– Column expressions
– Table names
– Entire SELECT statements
Using the & Substitution Variable
- Use a variable prefixed with an ampersand (&) to prompt the user for a value:
SQL> SELECT ENAME,DEPTNO FROM EMP
WHERE DEPTNO=&DEPTNO;
Enter value for deptno: 10
old 2: WHERE DEPTNO=&DEPTNO
new 2: WHERE DEPTNO=10
ENAME DEPTNO
———- ———-
CLARK 10
KING 10
MILLER 10
Using the && Substitution Variable
- Use the double ampersand (&&) if you want to reuse the variable value without prompting the user each time:
SQL> SELECT &ENAME,&SAL,&&DEPTNO FROM EMP;
ENAME SAL DEPTNO
———- ———- ———-
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
************************************************************************
For more details and queries please feel free to email, visit or call us. Wishing you the very best for all your future endeavors.
Helpline: 9814666333, 8699444666
Email: info@technocampus.co.in
Please fill the form and we shall contact you soon.
oracletechnocampus 