Oracle SQL - Implicit SQL*Plus User-Defined Variables

  1. HOME
  2. Oracle SQL
  3. SQL Plus
  4. Define System Variable

Introduction

SQL*Plus allowing you to define variables implicitly.

Then you start with substitution variables in your SQL and SQL*Plus commands, SQL*Plus prompts for a value only once.

You can implement this behavior by using double ampersands (&&).

The following code shows that you start out without an my_var variable, you are prompted for a value only once, and then an implicit DEFINE is executed. 

SQL> define enr
SP2-0135: symbol enr is UNDEFINED

SQL> select * from emp
  2  where  empno between &&enr and &enr+100;

Enter value for enr: 1234

SQL> define my_var
DEFINE my_var             = "1234" (CHAR)
SQL>

If you re-execute the contents of the SQL buffer (with / or RUN), there is no prompting at all; the stored my_var value (1234) is used.

So if you use this technique, make sure to end (or start) your scripts with the appropriate UNDEFINE commands.

Related Topic