A Guide to “Substitution Variables” in Oracle SQL

“Substitution variables” in Oracle SQL are a powerful feature for executing queries dynamically. This article provides a detailed explanation, from practical examples using the SCOTT schema to applied techniques, designed for beginners to understand intuitively.

This article uses examples based on the SCOTT sample schema.
You can import and use the SCOTT sample schema by executing the following:

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger

1. What are Substitution Variables?
2. How to Use Temporary Substitution Variables (&)
3. How to Utilize Persistent Substitution Variables (&&)
4. Practical Example of Dynamic Queries (Specifying Multiple Conditions)
5. Management Commands
6. Common Troubles and Solutions
7. Points to Note and Best Practices
8. Summary

1. What are Substitution Variables?

A substitution variable is a mechanism for specifying dynamic values within an SQL query. By entering a value when the query is executed, the same query can be used flexibly with different conditions.

Use Cases

Specifying conditions for report generation
Simple interactive queries
Creating reusable scripts

The explanation below proceeds using the SCOTT schema as an example. The SCOTT schema is a sample dataset provided standard with Oracle for learning purposes.

2. How to Use Temporary Substitution Variables (&)

Temporary substitution variables require you to input a value at query execution time. Their feature is that a different value can be specified every time.

Basic Example

The following query searches for employees by specifying the DEPTNO (department number).

SELECT empno,ename,job,deptno
FROM EMP
WHERE DEPTNO = &deptno;

Execution Steps

Enter the value at the prompt: Enter value for deptno: 10
The query is executed with the specified department number (e.g., 10).

Result Example

SQL> SELECT empno,ename,job,deptno
  2  FROM EMP
  3  WHERE DEPTNO = &deptno;
Enter value for deptno: 10  ★Enter 10
old   3: WHERE DEPTNO = &deptno
new   3: WHERE DEPTNO = 10

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10

3. How to Utilize Persistent Substitution Variables (&&)

Persistent substitution variables retain the value entered once within the session. This is convenient when executing repeatedly with the same condition.

Basic Example

SELECT empno,ename,job,deptno
FROM EMP 
WHERE DEPTNO = &&deptno;

Execution Steps

Enter the value at the first execution: Enter value for deptno: 20
Within the session, the same value is used without re-entry.

If re-entry is needed

Reset the variable with the following command.

UNDEFINE deptno;

4. Practical Example of Dynamic Queries (Specifying Multiple Conditions)

By combining multiple substitution variables, more flexible searches are possible.

Conditional Search Example

Search for applicable employees by specifying the department number and the minimum salary.

COLUMN ENAME FORMAT A15
COLUMN SAL FORMAT 99999

SELECT ENAME, JOB, SAL 
FROM EMP 
WHERE DEPTNO = &deptno
  AND SAL > &min_salary;

Execution Steps

Enter DEPTNO and MIN_SALARY: Enter value for deptno: 30 Enter value for min_salary: 1500
Data matching the conditions is displayed.

Result Example

SQL> SELECT ENAME, JOB, SAL
  2  FROM EMP
  3  WHERE DEPTNO = &deptno
  4    AND SAL > &min_salary;
Enter value for deptno: 30  ★Enter 30
old   3: WHERE DEPTNO = &deptno
new   3: WHERE DEPTNO = 30
Enter value for min_salary: 1500  ★Enter 1500
old   4:   AND SAL > &min_salary
new   4:   AND SAL > 1500

ENAME           JOB          SAL
--------------- --------- ------
ALLEN           SALESMAN    1600
BLAKE           MANAGER     2850

5. Management Commands

Checking Substitution Variables (DEFINE)

Checks the currently set substitution variables.

DEFINE deptno;

Deleting Substitution Variables (UNDEFINE)

Deletes the variable and prompts for re-entry.

UNDEFINE deptno;

6. Common Troubles and Solutions

Trouble	Cause	Solution
Value is retained and cannot be changed	You are using `&&`.	Reset the variable using `UNDEFINE`.
Prompt is not displayed	`SET DEFINE OFF` is enabled.	Execute `SET DEFINE ON`.
Prompt is displayed multiple times	The same variable name is used repeatedly with `&`.	Use a persistent variable (`&&`) to skip re-entry.

7. Points to Note and Best Practices

Security Risk: Inserting user input directly into SQL carries a risk of SQL injection. Validation of input values is essential, especially in a production environment.
Variable Name Clarity: Use meaningful variable names that are not too short (e.g., deptno or min_salary).
Reusable Scripts: Scripts that use variables are highly reusable and streamline daily operations.

8. Summary

This article explained the basics and applications of creating dynamic queries using “substitution variables” with the SCOTT schema. We provided practical examples that even beginners can use immediately, supporting the improvement of practical SQL skills.

Please try this in your own environment using this article as a reference! Feel free to send any questions or feedback.

[reference]
Oracle Database SQL Language Reference, 19c