Oracle SQL*Plus: The Definitive Guide

The DEFINE and UNDEFINE Commands

The DEFINE and UNDEFINE commands allow you to explicitly create and delete user variables. DEFINE creates a variable and assigns it an initial value. DEFINE also lets you list all currently defined user variables with their values. The UN-DEFINE command allows you to delete a user variable so it can no longer be referenced.

The DEFINE Command

The DEFINE command is used to define a new user variable and assign it a value. DEFINE may also be used to display the value of a specific user variable or to display the values of all user variables.

Syntax for the DEFINE command

The syntax for the DEFINE command is:

DEF[INE] [variable_name [= text]]

where:

DEF[INE]

Is the command, which may be abbreviated to DEF.

variable_name

Is the name of the variable you want to create.

text

Is the text you want to assign to that variable. This may optionally be enclosed by single or double quotes, which you should use any time the value contains spaces or any other nonalphabetic character.

Defining a variable

The first form of the DEFINE command is used to create a variable. Here are some examples:

SQL> DEFINE fiscal_year = 1998
SQL> DEFINE my_publisher = "O'Reilly"
SQL> DEFINE my_editor = Debby Russell

The last command is a good example of where quotes should have been used. The command will appear to execute correctly, but because of the space between the first and last name, my_editor will contain just “Debby”. The remaining portion of the line is ignored.

Examining a variable ...