Thursday, January 13, 2011

Substitution variables

I found veri nice article about substitution. Check hier.
Basically if we want to be prompt every time to put value in a script when ampersand ocure, we have to put single ampersan &. If we want to be prompt just once for a value, we need to use double ampersand like this &&.
For example:
select first_name, last_name from employees
where salary > &salary;
 Every time we execute that statemet it will promt for a value

select first_name, last_name from employees where salary > &&salary;
 It will promt us for a value just first time we execute the statement, because the variable is stored as a session variable.
Oracle provides also mechanism to undefine that session variable. It can be done using
UNDEFINE variable;
In our care will be UNDEFINE salary;

We have also posibilities to retrieve list with all defined variables by typing
DEFINE;

Second use of define command is to define variable during session.
DEFINE variable=value;

No comments:

Post a Comment