You ever had a time when you wanted to run a SQL script, but the values it used had to change dynamically every time you run it?  I sure have.  Oracle itself (believe it or not) proposes two solutions to this task.  One is to use the substitution variable marker & or the &&.  What's the difference between the two?   Basically, the & marker will tell SQL*Plus that what follows is a substitution variable.  Prompt for it using the name of the variable itself.  Now, && basically does the same thing.  In fact, in most cases the & can safely be substituted for the &&.  However there is one big gotcha that Oracle does not document - rather it implies this caution in its documentation.

Every time SQL*Plus runs into a variable with a & in front of it, a value for that variable will be prompted for - even if that same variable had previously been prompted for.  If instead, you use && in front of a variable, Oracle will prompt for it only one time.  If that same variable (with the && in front of it) is found later in the query, then the value that was first received will be substituted for at every occurance of that variable.  Oracle explains this quite well in it's documentation, but there is one thing they don't warn about: re-runs of the scripts.

If you are still at the SQL> prompt after you run a script with && substitution variables in front of it, the values you entered will be re-entered again.   You won't be prompted for a new value.  This can be done over and over again, but no prompting will be done - ever.  The only way I've found to clear this is to exit from SQL*Plus and then come back into it again.  Had you been using the & command in front of your substitution variables, then a re-run would result in re-prompting for values.

Now you can mix & and && variables.  That's allowable and to be encouraged.  Use the && variables in places where multiple runs won't make a difference - the values will stay the same anyway.  Use the & variables in places where values in a query (or update or delete) really will change.

Tips: The way to reset a '&&' substitution variable is to use the UNDEF <variable_name> command. It is not necessary to exit and re-enter sql*plus.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips