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

The second form of the DEFINE command, where you specify only a variable name as an argument, shows you the contents of that variable. Here are some that examine the variables just created previously:

SQL> DEFINE fiscal_year
DEFINE FISCAL_YEAR     = "1998" (CHAR)
SQL> DEFINE my_publisher
DEFINE MY_PUBLISHER    = "O'Reilly" (CHAR)
SQL> DEFINE my_editor
DEFINE MY_EDITOR       = "Debby" (CHAR)

As you can see, because double quotes weren’t used in the original DEFINE command, Debby’s last name has been lost.

Listing all variables

Issuing the DEFINE command with no arguments at all tells SQL*Plus to display all defined variables with their contents; for example:

SQL> DEFINE
DEFINE _SQLPLUS_RELEASE = "800040000" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle8 Enterprise Edition Release 8.1.3.0.0
With the Partitioning and Objects options
PL/SQL Release 8.1.3.0.0 - Beta" (CHAR)
DEFINE _O_RELEASE      = "801030000" (CHAR)
DEFINE FISCAL_YEAR     = "1998" (CHAR)
DEFINE MY_PUBLISHER    = "O'Reilly" (CHAR)
DEFINE MY_EDITOR       = "Debby" (CHAR)
SQL>

Not only are the variables just defined in the above list, but there are a number of others as well. That’s because SQL*Plus automatically defines these at startup. You can define your own variables automatically as well, by taking advantage of the LOGIN.SQL file. See Section 11.2" in Chapter 11, for more information on this.

Usage notes

If you need to get a value from a user, DEFINE doesn’t buy you much because you still have to use the ACCEPT command to get the user’s input. However, using the DEFINE command to explicitly create all your variables at the beginning of a script can serve as a useful form of documentation. This is especially helpful in very long scripts, or when you have a chain of tightly coupled script files that call one another.

The DEFINE command can also be used to define certain magic constants, such as a company name, that may need to be used more than once in a script. Storing these types of constants in a user variable makes the script more maintainable because changes to the values can be made in one central place.

The UNDEFINE Command

The UNDEFINE command deletes a variable definition. If you have created a variable containing sensitive information, such as a password, you can use UN-DEFINE to delete it when it is no longer needed. It’s also not a bad idea to UN-DEFINE all your variables at the end of a script, so they don’t linger and possibly affect the execution of other scripts that you run.

Syntax for the UNDEFINE command

The syntax for UNDEFINE looks like this:

UNDEF[INE] variable_name [ variable_name...]

where:

UNDEF[INE]

Is the command, and may be abbreviated to UNDEF.

variable_name

Is the name of a user variable to delete. You can delete several variables with one command by listing them out, separated by spaces.

Deleting a variable

The following example deletes the three variables created earlier when the DEFINE command was discussed:

SQL> UNDEFINE fiscal_year
SQL> UNDEFINE my_publisher my_editor
SQL> DEFINE my_publisher
symbol my_publisher is UNDEFINED
SQL> DEFINE my_editor
symbol my_editor is UNDEFINED
SQL> DEFINE fiscal_year
symbol fiscal_year is UNDEFINED

As you can see, after using UNDEFINE to delete the variables, they can no longer be referenced.