|SQL*Plus User's Guide and Reference
Command Reference, 18 of 52
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:
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
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 DEPTNO, type:
Even though you enter the number 20, SQL*Plus assigns a CHAR value to DEPTNO consisting of two characters, 2 and 0.
To list the definition of DEPTNO, enter
This result shows that the value of DEPTNO is 20.