Using
the DEFINE Command
The example
shown creates a substitution variable for an employee number by using the
DEFINE command. At run time, this displays the employee number, name, salary,
and department number for that employee.
Because the
variable is created using the SQL Developer 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
EMPLOYEE_NUM substitution variable is present in the session until the user
undefines it or exits the SQL Developer session.
DEFINE
employee_num = 200
SELECT
employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;
UNDEFINE
employee_num
Using
the VERIFY Command
SET
VERIFY ON
SELECT
employee_id, last_name, salary
FROM employees
WHERE employee_id = &employee_num;
To confirm
the changes in the SQL statement, use the VERIFY command. Setting SET VERIFY ON
forces SQL Developer to display the text of a command after it replaces
substitution variables with values. To see the VERIFY output, you should use
the Run Script (F5) icon in the SQL Worksheet. SQL Developer displays the text
of a command after it replaces substitution variables with values.
The example
above displays the new value of the EMPLOYEE_ID column in the SQL statement
followed by the output.
SQL*Plus
System Variables
SQL*Plus
uses various system variables that control the working environment. One of the
variables is VERIFY. To obtain a complete list of all the system variables, you
can issue the SHOW ALL command on the SQL*Plus command prompt.
