Oracle Substitution Variables and Script Markers in Oracle SQL*Plus

Utilizing substitution variables in Oracle SQL*Plus is an efficient technique to input values dynamically when executing scripts. It allows users to define variables and assign values to them during script execution, enhancing script flexibility and reusability.

Types of Substitution Variables:

1. User Variables: Initiated by the user to store values that can be referenced within a session.
   Syntax:

   DEFINE variable_name = value;
   

   
   Example:

   DEFINE id = 100;
   SELECT * FROM employees WHERE employee_id = &id;
   

2. System Variables: Predefined variables that control SQL*Plus behavior, such as `LINESIZE` and `PAGESIZE`.
   Syntax:

   SET variable_name value;
   

   
   Example:

   SET LINESIZE 60;
   

3. Bind Variables: Variables prefixed with a colon that can hold values, used in PL/SQL blocks and SQL statements.
   Example:

   VARIABLE v_id NUMBER;
   EXEC :v_id := 100;
   SELECT * FROM employees WHERE employee_id = :v_id;
   

4. Prompting for Values:
   Utilize the `ACCEPT` command to prompt users for specific values during script execution.
   Syntax:

   ACCEPT variable_name [text] PROMPT prompt_message;
   

   
   Example:

   ACCEPT v_id NUMBER PROMPT 'Please enter an employee ID: ';
   SELECT * FROM employees WHERE employee_id = &v_id;
   

   
   In the example, `v_id` is the variable name, `NUMBER` is the datatype, and `'Please enter an employee ID: '` is the prompt message displayed to the user.
5. Utilizing the Ampersand (&) When executing a statement, utilize the ampersand (&) preceding the variable name to substitute its value into the SQL statement.
   Example:

   SELECT * FROM employees WHERE employee_id = &v_id;
   

Implementing substitution variables in Oracle SQL*Plus enhances the automation and adaptability of database scripts, providing efficient and dynamic operations. Proper understanding and usage of different types of substitution variables and commands for prompting user values optimize the efficiency and versatility of SQL*Plus as a robust database management and scripting tool.

SQL*Plus allows you to use a kind of variable called a substitution variable or user variable. Substitution variables are markers that you can place in a script to mark places where you want to supply a value at the time you execute the script. Let us revisit the SQL query for the database object report that you created earlier.

SELECT owner,
       object_type,
       count(*) object_count,
       TO_CHAR(MAX(last_ddl_time),'dd-Mon-yyyy')
    last_ddl_time
FROM dba_objects
GROUP BY owner, object_type
ORDER BY owner, object_type;

As it's written, this script returns information for all schemas^[1] (owners) in the database. Using substitution variables, you can have SQL*Plus prompt you for a an owner name each time you run the script, and then display the database objects for that owner.

SELECT owner,
       object_type,
       count(*) object_count,
       TO_CHAR(MAX(last_ddl_time),'dd-Mon-yyyy')
    last_ddl_time
FROM dba_objects
WHERE owner = '&user_name.'
GROUP BY owner, object_type
ORDER BY owner, object_type;

Notice the user_name. construct. The ampersand (&) character in SQL*Plus is used to mark the beginning of a variable. The period (.) marks the end of the variable. In this case, the variable name is user_name.
When you place substitution variables in your scripts, SQL*Plus will prompt for a value using a standard "Enter a value for variable_name:" prompt. You will get the prompt when you run the script. This simulation shows you how that process works:
Substitution Variables
The ending period for a substitution variable is not necessary if the variable name is followed by a space, or by some other punctuation. In this example, ‘&user_name’ could have been used without the period, and most of the time that is what you will see in other people’s scripts. Substitution variables provide a wonderful mechanism for collecting user input with scripts. The next lesson will show you how you can generate more user-friendly prompts using the SQL*Plus PROMPT command.

You can use substitution variables anywhere in SQL and SQL*Plus commands, except as the first word entered. When SQL*Plus encounters an undefined substitution variable in a command, SQL*Plus prompts you for the value. You can enter any string at the prompt, even one containing blanks and punctuation. If the SQL command containing the reference should have quote marks around the variable and you do not include them there, the user must include the quotes when prompted. SQL*Plus reads your response from the keyboard, even if you have redirected terminal input or output to a file. If a terminal is not available (if, for example, you run the script in batch mode), SQL*Plus uses the redirected file. After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. You can suppress this listing by setting the SET command variable VERIFY to OFF.

[1] schema: A schema is named collection of database objects. In Oracle, schemas are tightly linked to users, each user has one schema with a name matching the user's name, and the term schema is sometimes used synonymously with user.