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 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.
The syntax for the DEFINE command is:
DEF[INE] [variable_name
[=text
]]
where:
Is the command, which may be abbreviated to DEF.
Is the name of the variable you want to create.
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.
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.
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.
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.
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 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.
The syntax for UNDEFINE looks like this:
UNDEF[INE]variable_name
[variable_name
...]
where:
Is the command, and may be abbreviated to UNDEF.
Is the name of a user variable to delete. You can delete several variables with one command by listing them out, separated by spaces.
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.