Oracle SQL - SQL*Plus User-Defined Variables


To store the value of a SQL*Plus variable so you can use it multiple times, use user-defined variables.

You can use the SQL*Plus DEFINE command to declare user-defined variables and to assign values to them.

The following code shows assigning Values to User-Defined Variables with DEFINE

SQL> define x=1234

SQL> select ename from emp
  2  where  empno between &x and &x+100;


The DEFINE command stores the user-defined variable X with its value 1234.

That's why SQL*Plus doesn't prompt for a value for X anymore.

Using the DEFINE command, you can display the value of a specific variable.

You can display a complete listing of all user-defined variables by not specifying a variable name and just entering the DEFINE command itself.

The SQL*Plus UNDEFINE command allows you to remove a user-defined variable.

The following code demonstrates examples of DEFINE and UNDEFINE.

SQL> def x
DEFINE X               = "1234" (CHAR)

SQL> def
DEFINE _DATE           = "02-FEB-2018" (CHAR)
DEFINE _USER           = "BOOK" (CHAR)
DEFINE _EDITOR         = "vim" (CHAR)
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1201000100" (CHAR)
DEFINE X               = "1234" (CHAR)

SQL> undefine x

Related Topic