Substitution Variables

Description:

Suppose that you want a query that lists the employees with various jobs and not just those whose job_ID is SA_REP.
You can edit the WHERE clause to provide a different value each time you run the command, but there is also an easier way.

By using a substitution variable in place of the exact values in the WHERE clause, you can run the same query for different values.
  
You can create reports that prompt users to supply their own values to restrict the range of data returned, by using substitution variables.

You can embed substitution variables in a command file or in a single SQL statement. A variable can be thought of as a container in which values are temporarily stored. When the statement is run, the stored value is substituted.

You can use single-ampersand (&) substitution variables to temporarily store values.

You can also predefine variables by using the DEFINE command. DEFINE creates and assigns a value to a variable.

Examples:

SELECT employee_id, last_name, salary, 

FROM   employees

WHERE  employee_id = &employee_num ;

The example creates a SQL Developer substitution variable for an employee number.

When the statement is executed, SQL Developer prompts the user for an employee number and then displays the employee number, last name, salary for that employee.

With the single ampersand, the user is prompted every time the command is executed if the variable does not exist.

SELECT last_name, department_id, salary*12

FROM   employees

WHERE  job_id = '&job_title' ;

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

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

SELECT employee_id, last_name, &column_name

FROM   employees

WHERE  &condition

ORDER BY &order_column ;

You can use the substitution variables not only in the WHERE clause of a SQL statement, but also as substitution for column names, expressions, or text.

SELECT   employee_id, job_id,&&column_name

FROM     employees

ORDER BY &column_name ;

You can use the double-ampersand (&&) substitution variable if you want to reuse the variable value without prompting the user each time. The user sees the prompt for the value only once.

In this example the user is asked to give the value for the variable, column_name, only once. The value that is supplied by the user (department_id) is used for both display and ordering of data. If you run the query again, you will not be prompted for the value of the variable.

UNDEFINE column_name

SQL Developer stores the value that is supplied by using the DEFINE command; it uses it again whenever you reference the variable name. After a user variable is in place, you need to use the UNDEFINE command to delete it.