|SQL*Plus User's Guide and Reference
Part Number A88827-02
Command Reference, 18 of 52
DEF[INE] [variable]|[variable = text]
Specifies a user variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables.
Refer to the following list for a description of each term or clause:
variable = text
Enter DEFINE followed by variable to list the value and type of variable. Enter DEFINE with no clauses to list the values and types of all user variables.
Defined variables retain their values until one of the following events occurs:
Whenever you run a stored query or command file, SQL*Plus substitutes the value of variable for each substitution variable referencing variable (in the form &variable or &&variable). SQL*Plus will not prompt you for the value of variable in this session until you UNDEFINE variable.
Note that you can use DEFINE to define the variable, _EDITOR, which establishes the host system editor invoked by the SQL*Plus EDIT command.
If you continue the value of a defined variable on multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return you enter with a space in the resulting variable. For example, SQL*Plus interprets
You should avoid defining variables with names that may be identical to values that you will pass to them, as unexpected results can occur. If a value supplied for a defined variable matches a variable name, then the contents of the matching variable are used instead of the supplied value.
Some variables are predefined when SQL*Plus starts. Enter DEFINE to see their definitions.
To assign the value MANAGER to the variable POS, type:
If you execute a command that contains a reference to &POS, SQL*Plus will substitute the value MANAGER for &POS and will not prompt you for a POS value.
To assign the CHAR value 20 to the variable DEPARTMENT_ID, type:
Even though you enter the number 20, SQL*Plus assigns a CHAR value to DEPARTMENT_ID consisting of two characters, 2 and 0.
To list the definition of DEPARTMENT_ID, enter
This result shows that the value of DEPARTMENT_ID is 20.