Oracle and PL/SQL Recipes: A Problem-Solution Approach

2-2. Qualifying Column and Variable Names

Problem

You have a variable and a column sharing the same name. You want to refer to both in the same SQL statement.

For example, you decide that you'd like to search for records where LAST_NAME is not equal to a last name that is provided by a user via an argument to a procedure call. Suppose you have declared a variable LAST_NAME, and you want to alter the query to read as follows:

SELECT first_name, last_name, email   INTO first, last, email   FROM employees   WHERE last_name = last_name;

How does PL/SQL know which LAST_NAME you are referring to since both the table column name and the variable name are the same? You need a way to differentiate your references.

Solution

You can use the dot notation ...