SQL*Plus Tasks - DEFINE Keyword

SQL*Plus can also create and manipulate its own in-memory variables, and it sets aside a few special variables that will affect its behavior. Actually, there are two separate types of variables in SQL*Plus: DEFINEs and bind variables. To assign a value to a DEFINE variable, you can use the DEFINE command:

SQL> DEFINE X = "UMASARATH"

To view the value of x, specify:

SQL> DEFINE X
DEFINE X               = "UMASARATH" (CHAR)
SQL>

You would refer to such a variable using an ampersand (&). SQL*Plus does a simple substitution before sending the statement to the Oracle database, so you will need single-quote marks around the variable when you want to use it as a literal string.

SQL> select '&x' from dual;

old   1: select '&x' from dual

new   1: select 'UMASARATH' from dual

'UMASARAT

---------

UMASARATH

SQL>