DEF[INE] [variable] | [variable = text]
Represents the user or predefined variable whose value you wish to assign or list.
Represents the CHAR value you wish to assign to variable. Enclose text in single quotes if it contains punctuation or blanks.
variable = text
Defined variables retain their values until you:
enter a new DEFINE command referencing the variable
Whenever you run a stored query or script, 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.
If the value of a defined variable extends over multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return with a space. For example, SQL*Plus interprets
DEFINE TEXT = 'ONE- TWO- THREE'
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.
To assign the value MANAGER to the variable POS, type:
DEFINE POS = MANAGER
If you execute a command containing a reference to &POS, SQL*Plus substitutes 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 = "20" (CHAR)
This result shows that the value of DEPARTMENT_ID is 20.
12.17.1 Predefined Variables
There are eight variables defined during SQL*Plus installation. These variables only differ from user-defined variables by having predefined values.
Table 12-3 Variables Predefined at SQL*Plus Installation
Connection identifier used to make connection, where available.
Current date, or a user defined fixed string.
Current version of the installed Oracle Database.
Full release number of the installed Oracle Database.
Privilege level of the current connection.
Full release number of installed SQL*Plus component.
User name used to make connection.
INSTANCE_NAME, SERVICE_NAME or
ORACLE_SID from the connection identifier. If a connection identifier is not supplied by the user during connection, the
_CONNECT_IDENTIFIER contains the
Contains either the current date as a dynamic variable, or a fixed string. The current date is the default and is formatted using the value of NLS_DATE_FORMAT.
Because _DATE can be used as a normal substitution variable, users may put it in TTITLE. If _DATE is dynamic and is used in TTITLE it will have all the normal variable semantics. If it is used with an ampersand than the value will be set to the time when the TTITLE command is executed. If it is used without an ampersand prefix, it will be re-evaluated for each page. For long reports with _DATE in the TTITLE or with multiple references to &_DATE, different times may be displayed for each occurrence of the variable.
Users using _DATE in TTITLEs will almost certainly want to use an ampersand: &_DATE, so that each page of the report has exactly the same timestamp. This is especially true when the current date format contains a "seconds" component.
A DEFINE (with no arguments) or dereference using &_DATE will give the current date.
The _DATE value can be UNDEFINED, or set to a fixed string with an explicit DEFINE _DATE.
You can re-enable the default dynamic date behavior with:
DEFINE _DATE = "" (an empty string)
_DATE enables time values to be included in your SQL*Plus prompt.
During SQL*Plus installation on Windows operating systems, it is set to Notepad. On UNIX operating systems, it is set to the value of the UNIX environment variable, EDITOR, if it exists, otherwise it is set to Ed.
DEFINE _EDITOR = vi
Contains the current version of the installed Oracle Database in the form:
Oracle Database 12c Release 18.104.22.168.0 - Beta
Contains the full release number of the installed Oracle Database in the form:
Contains a value indicating the privilege level of the current connection. It contains one of the following values:
An empty string for normal-user connections or when there is no connection.
AS SYSASM, AS SYSBACKUP, AS SYSDBA, AS SYSDG, AS SYSOPER and AS SYSRAC are database administrator level privileges.
GRANT for information on AS SYSDBA and AS SYSOPER privileges.
Contains the full release number of the installed SQL*Plus component in the form:
Contains the user name connected to the current connection.
You can view the value of each of these variables with the DEFINE command.
These variables can be accessed and redefined like any other substitution variable. They can be used in TTITLE, in '&' substitution variables, or in your SQL*Plus command-line prompt.
You can use the DEFINE command to view the definitions of these nine predefined variables in the same way as you view other DEFINE definitions. You can also use the DEFINE command to redefine their values, or you can use the UNDEFINE command to remove their definitions and make them unavailable.
To view a specific variable definition, enter
where variable is the name of the substitution variable whose definition you want to view.
To view all predefined and user defined variable definitions, enter
All predefined and all user defined variable definitions are displayed.
You can use UNDEFINE to remove a substitution variable definition and make it unavailable.
Examples of Use of Predefined Variables
To change your SQL*Plus prompt to display your connection identifier, enter:
SET SQLPROMPT '_CONNECT_IDENTIFIER > '
The value of the predefined variable _SQLPLUS_RELEASE is displayed.
DEFINE _SQLPLUS_RELEASE = "1201000100" (CHAR)