1-5. Accepting User Input from the Keyboard
Problem
You want to write a script that prompts the user for some input. You want your PL/SQL code to then use that input to generate some results.
Solution
SQL*Plus provides a facility to accept user input. Use ampersand (&) character to indicate that a particular value should be entered from the keyboard. Here's an example:
DECLARE
emp_count NUMBER;
BEGIN
SELECT count(*)
INTO emp_count
FROM employees
WHERE department_id = &department_id;
END;
If the previous block is executed from SQL*Plus, you will see the following text, which prompts you to enter a department ID. In this case, the department ID of 40 is used.
Enter value for department_id: 40 old 7: WHERE department_id = &department_id; ...Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
