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.