What is substitution variables in SQL with real life examples? | OCA-4

In my previous article I have explained how to prepare for OCA with simple select statements examples. In this article I would like to throw light on substitution variables with real life examples. This will give you clear idea about substitution variables in oracle and how you can use the substitution variable in detail. The substitution variables are nothing but temporary variables which is used for reusability purpose. We are using ampersand or double ampersand to store the values. The substitution variables are important to give the inputs to SQL statements.

What we will find in this article?

1. Substitution variable definition
2. & substitution variable with multiple examples
3. DEFINE statement of Substitution variable
4. Prompt statement of Substitution variable

Substitution variable in SQL definition:

The substitution variables are temporary variables using which user give the input conditions for select statements. There are two types of substitution variables.

& substitution variable

&& substitution variable

& Substitution Variables :

If we require to check the results if any issues in select statement for the application, we need to use the substitution variable. You can use substitution variable in select statement, order by clause or where condition.

Real life example for HR schema on Oracle developer

If you want to execute the following statement using substitution variable

Select employee_id,First_name,job_id from Employees where employee_id=&Emp_no;

The above query will ask input for Employee_id in box format. User needs to give employee_id to fetch the data. These kind of statements are useful in troubleshooting the issues.

Select employee_id,first_name,job_id,&salary_condition

From Employees where &where_condition order by  &order_condition;

You can give inputs to salary_condition as 10000 and where_condition as salary>7000 as salary condition and order condition as Employee_id.  The query will give  you the results of input.

If you can use the following SQL statement on sql plus that will give you old data and new data.

If we need to define the specific value for the substitution variable, we can DEFINE that variable. When you use single ampersand then the value will be discarded immediately after one time execution. In that cases we require to use DEFINE statement. The DEFINE statement will be used to define the value for whole session.

Example :

DEFINE Employee_Num = 100;  — DEFINE variable for session

Select employee_id,first_name,salary from Employees where Employee_id= &employee_num;

UNDEFINE Employee_num;

The UNDEFINE statement will be used to UNDIFINE the variable for the session.

Prompt Statement of Substitution variable:

We have seen already the substitution variable and defining substitution variable.The variable will be discarded after the session close. User can change the Prompt message by defining the substitution variable using prompt statement.

Syntax :

ACCEPT column_Name Prompt “MESSAGE”;

Example :

IF you want to DEFINE the employee_num variable and want to change the Input message then use following statement,

ACCEPT Employee_Num Prompt “ Kindly Enter Employee Number :”;

Select * from Employees where Employee_Num = &Employee_Num;

UNDIFINE Employee_Num;

These are some most important examples of & substitution variable and various ways to define the substitution variable.

&& Substitution variable :

&& variable is used to define the variable and assign the value at same time.  We do not need to use the DEFINE variable in case of && variable. DEFINE = && in case of substitution variable.

Example :

Select Employee_num,First_name  from Employees where  Employee_num = &&Column_name;

For Undefining the variable you require to use UNDEFINE statement.

Verify statement :

Verify statement will give you the information about old query and new query after replacing substitution variable in value. You just require to use following query before main query.

SET VERIFY ON;

Select Employee_num,First_name  from Employees where  Employee_num = &&Column_name;

The above statement is used to get old query and new query.

Let see one more example,

Select * from Employees where First_name like ‘%&A&’;

The above statement sometimes will give you error as it will consider the % % operator as substitution variable. We need to use following statement before execution of above ,

SET DEFINE OFF;

These kind of errors will happen in case of migration projects.

These are some important examples of substitution variables in SQL with real life examples. I hope you like this article on substitution variables in SQL in detail. If you like this article or if you have any issues with the same kindly comment in comments section.