Substitution Variables in Oracle

Let me start by saying that substitution variables are not a feature of the SQL language itself, but a feature of the tools we use to interact with the database, such as SQL Developer or SQL Plus, but I have included it here because it is very useful, and also because, for some strange reason, the SQL certification exam includes this topic, and I know many people use this course to prepare for the exam.

The concept is very simple, and I’m going to show you with an example.

Let’s say I have this query to get the list of employees who work in the department with ID 1.

SELECT *
FROM employee
WHERE department_id = 1;

But now I want to list employees from department 2, so, I could modify my query directly, or I could make a copy and modify the copy, in case I want the old version again.

I could need to do it for department 4 a little later, so, I would need to make another copy and modify it again, but there is an easier way to handle this situation:

Instead of writing the department id in my query directly, I could use a variable, which could take a different value each time I run the query, instead of having to modify the query every time, or have many different variants of the query.

So, how do I do that?

It’s very easy.

Parameterizing a Query by Adding Substitution Variables

I just have to write an ampersand (&), and then the name of the variable I want to use, so, in this case, I’m going to call it “department”.

So, my query would look like this

SELECT *
FROM employee
WHERE department_id = &department;

Now, look what happens when I run the query:

Here I just have to enter the department_id I want to use, and the query will be executed as if I had written it using a literal, like in my original query.

Now, if I execute it again, it will ask for the value I want to use as well, so I can now enter a different department id, and get my results using the same query without modifications.

But please keep in mind that this substitution is done by the client tool (SQL Developer in this case) before sending the statement to the database, so, the database doesn’t even know that I used a substitution variable, because it is sent by SQL Developer as if I had used the department id as a literal.

Using Substitution Variables for Strings and Dates

Now, if I want to use a substitution variable for a string (varchar), I have to make sure the value is enclosed inside single quotes, as usual, so, for example, if I have this query to filter employees by job_id:

SELECT *
FROM employee
WHERE job_id = &job;

I would need to enter the value I want to use inside single quotes, like ‘HR_REP’ or similar.

Or I can simply enclose the variable inside the quotes in the query, to avoid having to write the quotes when entering the value, which I think is easier.

If I want to filter by a date column, and I want to use a variable in the place of a date literal, I could do something like this:

SELECT *
FROM employee
WHERE hire_date >= DATE '&date';

This way I just have to enter the string that represents my date, in the format you know must be used for date literals (YYYY-MM-DD).

Go ahead, test the above query in your database.  Remember that playing with things is the best way to learn them.

Referencing  Substitution Variables more than Once

Now I want to show you what happens if I have a query in which I use the same variable more than once.

Take a look at this query.

SELECT *
FROM employee
WHERE salary = &sal
OR salary = &sal * 2;

I want this query to return employees who earn the value I enter, or the double of that value, but if you test it, you will notice that SQL Developer asks for the value twice (once for each time the variable is used).

This is not only impractical, but it also allows me to enter a different value in the second prompt, and that would make the query produce unexpected results.

This happens because when you define variables this way, the value is not stored anywhere.  The variable is just substituted by the value and the value is discarded, so if the variable appears again, SQL Developer will prompt for a value again.

This is usually not what you want, because if you wanted to use a different value, you would have used a different variable name, don’t you think?

So, can we change this behavior?

Yes, we can!

Saving Substitution Variables’ Values

If you want to save the value you provided, so that you are not asked to provide it twice if the variable appears twice, you just have to use a double ampersand, instead of a single one, like this:

SELECT *
FROM employee
WHERE salary = &&sal
OR salary = &sal * 2;

You just need to add the additional ampersand to the first appearance of the variable.  You can add it to the other appearances if you want to, but it doesn’t make any difference.

So, if I run the query now, SQL Developer would only ask me for the value once, and the same value would be used in any other part of the statement where the same variable is used.

Now, go ahead and run the above query at least two times, and continue reading when you are ready.

What happened?

You didn’t get prompted for the value even once, did you?

Making SQL Developer Forget the Saved Value of a Substitution Variable

This happens because once you define a variable with a double ampersand, the value is stored for the duration of the session, so, if you want to get prompted again, you have to explicitly undefine the variable, which is done precisely with a command called “UNDEFINE”.

So, when you use a double ampersand to define a variable, it is usually a good idea to run the UNDEFINE command immediately after the SQL statement, to make sure you don’t end up using a previously used value by mistake.

Again, remember that this is not a feature of the SQL language, but of the application we are using to interact with the database, which in this case is SQL Developer.

This UNDEFINE command is actually an SQL*Plus command, which is supported by SQL Developer and some other tools as well.

Okay, and to conclude the lesson, here is your task.

When you finish, please continue to the next lesson.

See you there.

Practice Challenge

• Write a query to list employees who were hired between a certain range of dates (for example, between January 1st, 2011 and December 15th, 2014). The query must be written in a way that allows the user to use a different date range without needing to modify the query.

Expected Output:

*  The output can vary depending on the values entered for the substitution variables at runtime.

• Write a query to list employees who work in a given department. The query must prompt the user to enter a value, and then return only employees whose salary is equal to the value entered, or equal to the half of the value entered or equal to a third of the value entered by the user.

• The query must also prompt for the id of the department that the user wants to list.

• Your script must include the command necessary to erase the value entered for the salary so that if the query is executed again, the user is prompted for the salary value again.

Expected Output:

*  The output can vary depending on the values entered for the substitution variables at runtime.