Using The DEFINE Command With & Substitution Variable

SQL> define emp_num=1234

SQL> select empno,ename,sal deptno from emp where empno=&emp_num;

old   1: select empno,ename,sal deptno from emp where empno=&emp_num

new   1: select empno,ename,sal deptno from emp where empno=1234

no rows selected

SQL> define emp_num=7782

SQL> select empno,ename,sal deptno from emp where empno=&emp_num;

old   1: select empno,ename,sal deptno from emp where empno=&emp_num

new   1: select empno,ename,sal deptno from emp where empno=7782

    EMPNO ENAME         DEPTNO

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

     7782 CLARK           2450

The examples above creates an iSQL*Plus substitution variable for an employee number by using the DEFINE command, and at run time displays the employee number, name, salary, and department number for that employee.

Because the variable is created using the iSQL*Plus DEFINE command, the user is not prompted to enter a value for the employee number. Instead, the defined variable value is automatically substituted in the SELECT statement.

The emp_num substitution variable is present in the session until the user undefines it or exits the iSQL*Plus session.