RelationalDBDesign RelationalDBDesign


SQL* Plus CLI  «Prev  Next»
Lesson 13 Substitution variables
ObjectiveUse substitution variables to prompt for values.

When to use substitution variables

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;

How to use substitution variables

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.


Where and How to Use Substitution Variables

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.