SQL*Plus User's Guide and Reference
Release 9.0.1

Part Number A88827-02
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Command Reference, 18 of 52


DEFINE

Syntax

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

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

variable

text

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.

Usage

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

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

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

Keyboard icon
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:

Keyboard icon
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

Keyboard icon
DEFINE DEPARTMENT_ID
Screen icon
DEFINE DEPARTMENT_ID = "20" (CHAR)

This result shows that the value of DEPARTMENT_ID is 20.


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback