Oracle/PLSQL: Prompt user for a parameter value in SQLPlus

Question: In SQLPlus, I'd like to set up an SQL statement so that the user is prompted for a portion of the query condition.

Answer: You can use the & character to prompt a user for a value. We'll demonstrate how to prompt for both a numeric as well as a text value below:

Prompting for a numeric value

In our first example, we'll prompt the user for a supplier_id value.

In this example, we've entered the following SQL statement:

select * from suppliers
where supplier_id = &supplier_id;

SQLPlus will then prompt as follows:

Enter value for supplier_id:

In this example, we've entered 1. SQLPlus has then returned records for the following SQL statement:

select * from suppliers
where supplier_id = 1;

Prompting for a text value

In our second example, we'll prompt the user for a supplier_name value.

In this example, we've entered the following SQL statement:

select * from suppliers
where supplier_id = '&supplier_name';

Please note that the &supplier_name value is enclosed in single quotes since the supplier_name field is defined as a varchar2 field.

SQLPlus will then prompt as follows:

Enter value for supplier_name:

In this example, we've entered Microsoft. SQLPlus has then returned records for the following SQL statement:

select * from suppliers
where supplier_id = 'Microsoft';