Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from Oracle SQL*Plus: The Definitive Guide

Prompting for Values

The most reliable and robust method for getting input from the user is to explicitly prompt for values using the ACCEPT and PROMPT commands. The ACCEPT command takes input from the user and stores it in a user variable, and also allows you some level of control over what the user enters. The PROMPT command may be used to display messages to the user, perhaps supplying a short summary of what your script is going to accomplish.

There are several potential problems that arise when you simply place substitution variables in your scripts and rely on SQL*Plus’s default prompting mechanisms. All of these problems can be avoided through the use of the ACCEPT command. Table 4.1 provides a list of these problems together with a description of how the ACCEPT and PROMPT commands can be used to overcome them.

Table 4-1. Potential Problems with SQL*Plus’s Default Prompting

Potential Problem

Solution

Using double ampersands to define a variable in a script results in your not being prompted for a value the second time you run the script.

Use the ACCEPT command to prompt for a value. This works regardless of whether the variable has previously been defined.

Setting terminal output off, such as when spooling a report to a file, prevents you from seeing the prompts for substitution variables used in the query.

Use the ACCEPT command to prompt for these values earlier in the script, before the SET TERMOUT OFF command is executed.

The default prompt provided by ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata