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';
