PL/SQL – Substitution variables

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.

Commands:

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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s