Reads a line of input and stores it in a given user variable.
Syntax
ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]
Terms and Clauses
Refer to the following list for a description of each term or clause:
| variable | Represents the name of the variable in which you wish to store a value. If variable does not exist, SQL*Plus creates it. |
| NUM[BER] | Makes the datatype of variable the datatype NUMBER. If the reply does not match the datatype, ACCEPT gives an error message and prompts again. |
| CHAR | Makes the datatype of variable the datatype CHAR. The maximum CHAR length limit is 240 bytes. If a multi-byte character set is used, one CHAR may be more than one byte in size. |
| DATE | Makes reply a valid DATE format. If the reply is not a valid DATE format, ACCEPT gives an error message and prompts again. The datatype is CHAR. |
| FOR[MAT] | Specifies the input format for the reply. If the reply does not match the specified format, ACCEPT gives an error message and prompts again for a reply. The format element must be a text constant such as A10 or 9.999. See the COLUMN command in this chapter for a complete list of format elements. |
| Oracle date formats such as 'dd/mm/yy' are valid when the datatype is DATE. DATE without a specified format defaults to the Oracle NLS_DATE_FORMAT of the current session. See the Oracle7 Server Administrator's Guide and the SQL Language Reference Guide for information on Oracle date formats. | |
| DEF[AULT] | Sets the default value if a reply is not given. The reply must be in the specified format if defined. |
| PROMPT text | Displays text on-screen before accepting the value of variable from the user. |
| NOPR[OMPT] | Skips a line and waits for input without displaying a prompt. |
| HIDE | Suppresses the display as you type the reply. |
Examples
To display the prompt "Password: ", place the reply in a CHAR variable named PSWD, and suppress the display, enter
SQL> ACCEPT pswd CHAR PROMPT 'Password: ' HIDE
To display the prompt "Enter weekly salary: " and place the reply in a NUMBER variable named SALARY with a default of 000.0, enter
SQL> ACCEPT salary NUMBER FORMAT '999.99' DEFAULT '000.0' - > PROMPT 'Enter weekly salary: '
To display the prompt "Enter date hired: " and place the reply in a DATE variable named HIRED with the format "dd/mm/yy" and a default of "01/01/94", enter
SQL> ACCEPT hired DATE FORMAT 'dd/mm/yy' DEFAULT '01/01/94'- > PROMPT 'Enter date hired: '
To display the prompt "Enter employee lastname: " and place the reply in a CHAR variable named LASTNAME, enter
SQL> ACCEPT lastname CHAR FORMAT 'A20' - > PROMPT 'Enter employee lastname: '