3.4 Substitution Variables & Session Directives

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

3.4 Substitution Variables & Session Directives

Key Takeaways Explain This Topic AI

Single-ampersand (&variable) prompts the user for a value each time it is encountered and discards the value immediately after query substitution.

Double-ampersand (&&variable) prompts the user once on first encounter and persists the defined value for the duration of the client session.

The DEFINE command creates or inspects session character variables, while UNDEFINE removes variables from the session.

The ACCEPT command provides custom prompt text, datatype validation (NUMBER, CHAR, DATE), default values, and password masking with the HIDE clause.

The SET VERIFY ON command instructs the client environment to display the old and new SQL statement text before and after substitution variable replacement.

Last updated: August 2026

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:

SQL*Plus encounters &&column_name and prompts: Enter value for column_name: salary .

SQL*Plus defines a session variable column_name with the value 'salary' .

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 .