DEFINE

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.

Terms | Usage | Examples | Top

Refer to the following list for a description of each term or clause:

variable

Represents the user variable whose value you wish to assign or list.

text

Represents the CHAR value you wish to assign to variable. Enclose text in single quotes if it contains punctuation or blanks.

variable = text

Defines (names) a user variable and assigns it a CHAR value.

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.

Usage | Terms | Examples | Top

Defined variables retain their values until one of the following events occurs:

• you enter a new DEFINE command referencing the variable

• you enter an UNDEFINE command referencing the variable

• you enter an ACCEPT command referencing the variable

• you reference the variable in the NEW_VALUE or OLD_VALUE clause of the COLUMN command and reference the column in a subsequent SQL SELECT command

• you EXIT SQL*Plus

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

DEFINE TEXT = 'ONE-
TWO-
THREE'

as

DEFINE TEXT = 'ONE TWO THREE'

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.

Examples | Terms | Usage | Top

To assign the value MANAGER to the variable POS, type:

DEFINE POS = MANAGER

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:

DEFINE DEPARTMENT_ID = 20

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

DEFINE DEPARTMENT_ID

DEFINE DEPARTMENT_ID = "20" (CHAR)

This result shows that the value of DEPARTMENT_ID is 20.