Substitution Variables

Substitution variables in Oracle SQL are placeholders that are used to prompt for user input or dynamically substitute values in SQL queries. These variables are often used in scripts or tools like SQL*Plus, SQLcl, or Oracle SQL Developer to make queries more flexible, interactive, and reusable.

 

1. What Are Substitution Variables?

Substitution variables are variables that you can define and then use within an SQL query. When the query is executed, Oracle SQL will replace the substitution variable with the value that you provide, either manually or programmatically.

Substitution variables are commonly denoted by a preceding ampersand (&) or double ampersand (&&) in Oracle SQL.

• Single Ampersand (&): Prompts the user for input each time the query is run.
• Double Ampersand (&&): Substitutes the value only once during the session and remembers it for subsequent uses.

 

2. Syntax of Substitution Variables

The basic syntax of a substitution variable is:

SELECT column_name

FROM table_name

WHERE column_name = '&substitution_variable';

For example:

SELECT first_name, last_name

FROM employees

WHERE department_id = '&dept_id';

In this example, when you run the query, Oracle will prompt you to enter the value for dept_id.

 

3. Types of Substitution Variables

• Simple Substitution Variable: A single variable that is prompted for every execution.
• Example: &variable_name
• Multiple Substitution Variables: You can use multiple substitution variables in a single query.
• Example: SELECT * FROM employees WHERE department_id = '&dept_id' AND salary > '&salary';
• Double Ampersand (&&): This stores the value entered for the substitution variable for the session. It does not prompt the user every time.
• Example: SELECT * FROM employees WHERE department_id = '&&dept_id';

Once &&dept_id is substituted in the query, Oracle will not prompt the user again for the value of dept_id during the session.

 

4. How Substitution Variables Work in SQL*Plus

In SQL*Plus (and similar tools like SQLcl), the substitution process happens as follows:

1. When a query containing a substitution variable is executed, Oracle prompts the user for the value to substitute for the variable.
2. If the variable has already been assigned a value (using &&), Oracle will use the stored value without prompting the user again.

For example:

SELECT * FROM employees WHERE salary > '&salary';

• If this is the first time executing the query, Oracle will prompt the user: Enter value for salary:
• If the variable salary is entered as 5000, the query will be: SELECT * FROM employees WHERE salary > 5000;
• If &&salary had been used in the query, it would remember the 5000 value for the entire session and not prompt the user again.

 

5. Using Substitution Variables with Different Data Types

Substitution variables are flexible and can be used with different data types. However, it's important to ensure that the value entered for the variable is compatible with the data type of the column you're querying.

• Numeric:

SELECT * FROM employees WHERE salary > '&salary';

• String:

SELECT * FROM employees WHERE department_name = '&dept_name';

• Date: You can use substitution variables with date formats as well. Example:

SELECT * FROM employees WHERE hire_date > TO_DATE('&hire_date', 'YYYY-MM-DD');

 

6. Defining Substitution Variables in SQL Scripts

Substitution variables are especially useful in SQL scripts where you might want to reuse queries but with different input each time. You can define variables for common parameters and use them throughout the script.

DEFINE dept_id = 10

SELECT * FROM employees WHERE department_id = &dept_id;

You can also use the DEFINE command to set default values for variables before running the script.

 

7. Storing and Reusing Substitution Variable Values

When you use double ampersands (&&), Oracle stores the substitution variable's value for the duration of the session. This allows you to use the same value multiple times without having to prompt the user for it again.

SELECT * FROM employees WHERE department_id = '&&dept_id';

Once the user provides the value for &&dept_id (e.g., 10), Oracle will substitute it wherever &&dept_id is used in the session, without asking for input again.

 

8. How to Delete Substitution Variables

If you need to delete or clear a substitution variable during a session, you can use the UNDEFINE command in SQL*Plus or SQLcl.

UNDEFINE dept_id;

This removes the variable from memory, and Oracle will prompt the user for the variable’s value again if used later in the session.

 

9. Using Substitution Variables in INSERT, UPDATE, and DELETE Statements

Substitution variables can be used not only in SELECT queries but also in data manipulation statements like INSERT, UPDATE, and DELETE. This allows you to dynamically insert, update, or delete records based on user input.

• INSERT Example:

INSERT INTO employees (employee_id, first_name, last_name)

VALUES ('&emp_id', '&first_name', '&last_name');

• UPDATE Example:

UPDATE employees

SET salary = '&new_salary'

WHERE employee_id = '&emp_id';

• DELETE Example:

DELETE FROM employees

WHERE department_id = '&dept_id';

 

10. Working with Default Values for Substitution Variables

You can also assign a default value to a substitution variable in case the user does not provide one. This is done using the DEFINE command.

DEFINE dept_id = 10

SELECT * FROM employees WHERE department_id = '&dept_id';

If the user doesn’t provide a value for &dept_id, the default value (10) is used.

Alternatively, you can prompt the user to enter a value but provide a default in the prompt message:

SELECT * FROM employees WHERE department_id = '&dept_id';

This will prompt the user with the message: Enter value for dept_id [default: 10]:

 

11. Using Substitution Variables in ORDER BY, HAVING, and GROUP BY Clauses

Substitution variables are not restricted to the WHERE clause only. You can use them in various parts of the query, such as in ORDER BY, HAVING, and GROUP BY clauses.

• ORDER BY Example:

SELECT employee_id, first_name, salary

FROM employees

ORDER BY &sort_column;

If &sort_column is entered as salary, the query will sort by the salary column.

 

12. Limitations of Substitution Variables

While substitution variables are powerful, there are a few limitations:

• They can only hold a single value (except when using arrays or collections in PL/SQL).
• Substitution variables are not available within stored procedures or functions—they are specific to the SQL session or SQL*Plus.
• They do not work in dynamic SQL when using Oracle PL/SQL directly.

 

13. Example: Using Substitution Variables in a Script

-- Set substitution variables

DEFINE dept_id = 10

DEFINE salary_threshold = 5000

 

-- Use the variables in the query

SELECT employee_id, first_name, last_name

FROM employees

WHERE department_id = '&dept_id' AND salary > '&salary_threshold';

 

14. Conclusion

Substitution variables are a very useful feature in Oracle SQL that can be used to make your SQL scripts more interactive, dynamic, and flexible. They allow you to prompt the user for input, use default values, and reduce the need for hardcoded values in your queries.

Let me know if you need more examples or clarification on any specific point!