Substitution variables are variables placed in query which are resolved in runtime. You will be asked for them when running the query. Everything can be substituted excep the first ‘select’ word.
Single ampersand variables usage:
select * from countries where country_name = '&c_name';
Double ampersand variables usage:
select * from employees where first_name like '&&prefix%' and last_name like '&&prefix%';
You will be asked once nevertheless you used variable multiple times. This is due to the fact that variable was defined in session.
define; -- allows you to check all defined variables in current session; define myVar='AR'; -- allows you to set variable in current session; undefine myVar; -- allows you to clear definition of variable in current session; set verify on; -- client displays message what is the current value of the substitution variable in session when query is executed; set define off; -- allows to turn off the whole substitution mechanism;