3.4 Substitution Variables & Session Directives

3.4 Substitution Variables & Session Directives

Quick Answer: Substitution variables allow developers to create dynamic, parameterized SQL statements in client tools like SQL*Plus and Oracle SQL Developer. A single ampersand (&var) prompts for a value at runtime and discards it after statement execution. A double ampersand (&&var) prompts once and saves the value as a session variable. The DEFINE command manages session variables, ACCEPT prompts users with custom messages and data types (with HIDE for passwords), and SET VERIFY ON displays before-and-after query text.

---

Client-Side Processing Architecture

Substitution variables are not processed by the Oracle database server. Instead, they are evaluated and substituted on the client side (SQL*Plus, SQL Developer, or SQLcl) before the final SQL statement is sent across the network to the database engine.

Substitution can occur anywhere in a SQL statement, including:

• WHERE clause filter values
• ORDER BY column names or directions
• SELECT column expressions and list items
• Table names in the FROM clause
• Entire SQL clauses

---

Single-Ampersand (&) Substitution

A single ampersand (&) instructs the client tool to prompt the user for a value each time the variable name appears in the script. After the statement executes, the value is discarded and not saved in session memory.

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id = &dept_id;

When executed in SQL*Plus, the user receives an interactive prompt:

Enter value for dept_id: 60

Multiple Occurrences of Single-Ampersand Variables

If a single-ampersand variable appears multiple times in a single query or script, the user is prompted separately for each occurrence:

-- The user is prompted TWICE: once for WHERE and once for ORDER BY
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id = &dept_num
ORDER BY &dept_num;

---

Double-Ampersand (&&) Substitution

A double ampersand (&&) prompts the user for a value on its first encounter, replaces the variable in the statement, and automatically defines a session variable with that value. Subsequent references to &&var or &var in the same session reuse the saved value without prompting the user again.

-- The user is prompted only ONCE for column_name
SELECT employee_id, last_name, &&column_name
FROM employees
ORDER BY &column_name;

In this example:

1. SQL*Plus encounters &&column_name and prompts: Enter value for column_name: salary.
2. SQL*Plus defines a session variable column_name with the value 'salary'.
3. When it reaches &column_name in the ORDER BY clause, it reuses 'salary' without re-prompting.

---

Quoting Character and Date Variables

Substitution variables perform literal text replacement. When substituting strings or dates in SQL expressions, you must ensure appropriate single quotation marks surround the text.

Two Approaches to Quoting:

Approach A: Hardcoded Single Quotes in SQL (Standard Best Practice)

-- User types: King (without quotes)
SELECT employee_id, last_name, hire_date
FROM employees
WHERE last_name = '&last_name';

If the user inputs King, the client substitutes the text into WHERE last_name = 'King'.

Approach B: Quotes Supplied by User

-- User types: 'King' (with single quotes)
SELECT employee_id, last_name, hire_date
FROM employees
WHERE last_name = &last_name;

If the SQL query lacks single quotes and the user enters King without quotes, the resolved query becomes WHERE last_name = King. Because King lacks quotes, Oracle interprets it as a column name and throws ORA-00904: "KING": invalid identifier.

---

Managing Variables with DEFINE and UNDEFINE

The DEFINE Command

The DEFINE command allows creating, inspecting, and listing session character variables without interactive prompting:

-- Create or overwrite a session variable
DEFINE dept_id = 50;

-- Inspect the value of a specific variable
DEFINE dept_id;
-- Output: DEFINE DEPT_ID = "50" (CHAR)

-- List ALL defined session variables
DEFINE;

Once defined, using &dept_id in queries will use the defined value 50 without prompting the user.

The UNDEFINE Command

The UNDEFINE command removes a session variable from memory, forcing subsequent queries to prompt the user again:

-- Delete the session variable
UNDEFINE dept_id;

-- Now this statement will prompt the user
SELECT * FROM employees WHERE department_id = &dept_id;

---

The ACCEPT Command: Advanced User Input

The ACCEPT command creates a substitution variable by prompting the user with a customized prompt string, enforcing data types, providing defaults, or masking input.

Syntax

ACCEPT variable_name [ datatype ] [ FORMAT format_spec ] [ PROMPT 'prompt_text' ] [ DEFAULT default_val ] [ HIDE ]

Key Attributes

• datatype: Can be NUMBER, CHAR, or DATE.
• PROMPT 'text': Displays custom instructions to the user.
• HIDE: Masks user keystrokes (essential for entering passwords or confidential keys).
• DEFAULT val: Assigns a default value if the user presses Enter without typing.

-- Example: Prompt for salary with number validation and a default
ACCEPT min_sal NUMBER PROMPT 'Enter minimum salary [Default 3000]: ' DEFAULT 3000

SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= &min_sal;

-- Example: Secure password prompt using HIDE
ACCEPT app_pwd CHAR PROMPT 'Enter database password: ' HIDE

---

The SET VERIFY Directive

In SQL*Plus and SQL Developer, the SET VERIFY environment directive controls whether the client displays the query text before and after substitution variable replacement.

SET VERIFY ON
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = &dept_no;

When executed with input 20, the client outputs the verification trace before displaying query results:

old   3: WHERE department_id = &dept_no
new   3: WHERE department_id = 20

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        201 Hartstein                      13000
        202 Fay                             6000

To suppress this output in automated scripts, execute SET VERIFY OFF.