SQL* Plus CLI  «Prev 

Substitution Variables in Oracle

  1. We will begin the simulation at the SQL*Plus prompt. Type in the command @db_objects_for_user to run the script.
  2. SQL*Plus has encountered the substitution variable named user_name, and is now prompting you to supply a value. Type SYSTEM. Be sure that it is upper-case and then press enter.
  3. Using the value that you supplied, SQL*Plus has executed the query. Look at the middle of the screen. Notice that SQL*Plus has displayed both the old and new values of the line containing the substitution variable. This behavior is automatic, and allows you to confirm that the value that you typed in is being placed into the query correctly. This is the end of the simulation.

Defining Substitution Variables

You can define variables, called substitution variables, for repeated use in a single script by using the SQL*Plus DEFINE command. Note that you can also define substitution variables to use in titles and to save your keystrokes (by defining a long string as the value for a variable with a short name).


Defining a Substitution Variable

To define a substitution variable L_NAME and give it the value "SMITH", enter the following command: 
DEFINE L_NAME = SMITH

To confirm the variable definition, enter DEFINE followed by the variable name: 
DEFINE L_NAME


DEFINE L_NAME = "SMITH" (CHAR)
To list all substitution variable definitions, enter DEFINE by itself. Note that any substitution variable you define explicitly through DEFINE takes only CHAR values (that is, the value you assign to the variable is always treated as a CHAR datatype).
You can define a substitution variable of datatype NUMBER implicitly through the ACCEPT command. You will learn more about the ACCEPT command. To delete a substitution variable, use the SQL*Plus command UNDEFINE followed by the variable name.