Character and Date Values with Substitution Variables:

Use single quotation marks for date and character values.

In a WHERE clause, date and character values must be enclosed within single quotation marks. The same rule applies to the substitution variables. 

Enclose the variable in single quotation marks within the SQL statement itself.

Note: You can also use functions such as UPPER and LOWER with the ampersand. Use UPPER(’&job’) so that the user does not have to enter the job title in uppercase.  See examples.

SQL> select ename,deptno,sal*12 from emp where job='&job_title';

Enter value for job_title: analyst

old   1: select ename,deptno,sal*12 from emp where job='&job_title'

new   1: select ename,deptno,sal*12 from emp where job='analyst'

no rows selected

SQL> select ename,deptno,sal*12 from emp where job='&job_title';

Enter value for job_title: ANALYST

old   1: select ename,deptno,sal*12 from emp where job='&job_title'

new   1: select ename,deptno,sal*12 from emp where job='ANALYST'

ENAME         DEPTNO    SAL*12

---------- --------- ---------

SCOTT             20     36000

FORD              20     36000

SQL> select ename,deptno,sal*12 from emp where job=UPPER('&job_title');

Enter value for job_title: analyst

old   1: select ename,deptno,sal*12 from emp where job=UPPER('&job_title')

new   1: select ename,deptno,sal*12 from emp where job=UPPER('analyst')

ENAME         DEPTNO    SAL*12

---------- --------- ---------

SCOTT             20     36000

FORD              20     36000