Substitution Variables in Oracle SQL

Topic Introduction: In Oracle SQL, substitution variables are placeholders that allow you to pass dynamic values into your SQL statements at runtime. They are often used in SQL*Plus scripts, which is a command-line tool for executing SQL statements and scripts in Oracle databases. Substitution variables are denoted by an ampersand (&) followed by a variable name, and you can use them in various parts of your SQL code, such as in SELECT, INSERT, UPDATE, or DELETE statements.

Here's how substitution variables work in Oracle SQL:

Define a Substitution Variable:

You can define a substitution variable by using the DEFINE command in SQL*Plus, or you can prompt the user to enter a value for the variable. For example:

-- Define a substitution variable

DEFINE employee_id = 101;

-- Prompt the user for a value and assign it to a variable

ACCEPT department_id NUMBER PROMPT 'Enter Department ID: '

Use the Substitution Variable:

You can use the substitution variable in your SQL statements wherever you need it. For example:

SELECT *

  FROM employees

 WHERE employee_id = &employee_id;

If you defined the variable as employee_id = 101, then when you execute the SQL statement, Oracle will replace &employee_id with 101, and the query will retrieve data for employee ID 101.

Running the SQL Statement:

When you run the SQL statement containing a substitution variable, SQL*Plus will prompt you to enter the value for the variable if it's not already defined. If the variable is defined, it will use the value you specified during the definition phase. You can also reassign values to substitution variables using the DEFINE command.

Undefining a Substitution Variable:

You can undefine a substitution variable using the UNDEFINE command. For example:

UNDEFINE employee_id;

Substitution variables are useful for creating reusable SQL scripts and for allowing users to provide input dynamically without modifying the SQL code. However, they are specific to SQL*Plus and some other Oracle tools. In SQL development environments or programming languages, you would typically use bind variables or parameterized queries to achieve a similar result, which is more secure and efficient for applications.