ACCEPT and PROMPT Commands with SQL

Using Ampersand Substitution to Restrict and Sort Output at Runtime: ACCEPT and PROMPT Commands

Introduction to ACCEPT and PROMPT Commands

In Oracle SQL*Plus:

• ACCEPT is used to define interactive substitution variables by prompting the user for input.
• PROMPT is used to display messages to the user when prompting for input.

These commands are useful for creating interactive queries that require user input at runtime.

Using the ACCEPT Command

The ACCEPT command is used to prompt the user for a value and store that value in a substitution variable. This variable can then be used in SQL queries.

Syntax: 

ACCEPT variable_name PROMPT 'Prompt message: ' [TYPE {CHAR | NUMBER | DATE}] [DEFAULT default_value];

Examples of Usage

Prompting for a Department Name

ACCEPT dept_name PROMPT ‘Enter the department name: ‘;

This command prompts the user to enter the department name, which will be stored in the dept_name variable.

Prompting for a Sort Order with a Default Value 

ACCEPT sort_order PROMPT 'Enter the sort order (ASC/DESC) [ASC]: ' DEFAULT 'ASC';

In this example, if the user does not provide a value, ‘ASC’ will be used as the default.

Defining the Data Type 

ACCEPT min_salary PROMPT 'Enter the minimum salary: ' TYPE NUMBER;

Here, the min_salary variable is expected to contain a numeric value. SQL*Plus will check that the input is a number.

Using the PROMPT Command

The PROMPT command is used to display a message to the user in SQL*Plus. This message can provide information or instructions before the ACCEPT command prompts for input.

Syntax: 

PROMPT Message to display;

Examples of Usage

Displaying a Welcome Message 

PROMPT Welcome! Please enter the requested information;

This message informs the user that they need to enter some information.

Displaying Instructions Before Input 

PROMPT Please enter the search criteria as follows:

This message prepares the user to provide criteria for the next query.

Complete Example: Interactive Query with ACCEPT and PROMPT

Here is a complete example using ACCEPT and PROMPT to create an interactive SQL query: 

-- Display an instructional message
PROMPT Please enter the criteria to filter employees:
-- Prompt the user to enter the department name
ACCEPT dept_name PROMPT 'Enter the department name: ';
-- Prompt the user to enter the sort order, with a default value
ACCEPT sort_order PROMPT 'Enter the sort order (ASC/DESC) [ASC]: ' DEFAULT 'ASC';
-- Prompt the user to enter the minimum salary, specifying the data type
ACCEPT min_salary PROMPT 'Enter the minimum salary: ' TYPE NUMBER;
-- Execute the query using the provided values
SELECT name, salary
FROM employees
WHERE department = '&dept_name'
  AND salary >= &min_salary
ORDER BY salary &sort_order;

In this example:

• PROMPT displays messages to guide the user.
• ACCEPT requests specific values from the user and stores them in substitution variables.
• The query uses the substitution variables (&dept_name, &min_salary, &sort_order) to filter and sort the data based on the user’s input.

Best Practices

• Provide Clear Instructions: Use PROMPT to give clear and precise instructions to the user to facilitate data entry.
• Set Default Values: Use the DEFAULT option with ACCEPT to provide default values, making it easier for users and reducing the risk of errors.
• Validate Data Types: Specify the data type with TYPE in ACCEPT to ensure the input is in the expected format.

Conclusion

The ACCEPT and PROMPT commands in Oracle SQL*Plus allow you to create interactive queries where the user provides values at runtime. These commands make your SQL scripts more dynamic and adaptable to user input.