ACCEPT CLPPlus command
The ACCEPT CLPPlus command creates a variable with a specified name. Values can be assigned to this variable interactively in the CLPPlus interface or through a parameter read as part of a script that is run. The ACCEPT command is useful for storing values that you commonly use in SQL statements or in the SQL buffer.
Output of the command is by default displayed to the standard output of the CLPPlus interface.
Invocation
You must run this command from the CLPPlus interface or from within a CLPPlus script file.
Authorization
None
Required connection
None
Command syntax
Command parameters
-
variable-name
- Defines the variable name. You cannot use special symbols and
characters such as the forward slash (/) or at sign (@).
When you issue the ACCEPT command, you are prompted for the value of variable-name.
FORMAT format-string
- Defines the format assigned to the variable. The value you attempt
to assign to the variable must follow the format outlined.
For a character variable, the value of format-string is
An
, where n specifies the number of characters that can be used to display the variable. The data wraps if it is wider than the specified width.For numeric variables, the value of format-string can be one or more of the following characters:
-
$
- Displays a leading dollar sign. ,
- Displays a comma at the indicated position. .
- Displays a decimal point at the indicated position. 0
- Displays a zero at the indicated position. 9
- Displays a significant digit at the indicated position.
DEFAULT default-value
- The default value defined with this option is assigned to the variable when a user hits the ENTER key and does not provide any value when prompted. PROMPT text
- The value defined with this option is displayed in the prompt when the ACCEPT command is entered. HIDE
- When HIDE is specified, the value entered by the user is not echoed on the console.
Example
In the following example, the ACCEPT command
creates a variable named my_name
and prompts for
a value. The value John Smith is stored in
this variable. The DEFINE command displays the
value of the variable.
SQL> ACCEPT my_name
Enter value for my_name: John Smith
SQL> DEFINE my_name
DEFINE my_name = "John Smith"
The following example shows all the options used in the ACCEPT command.
SQL> ACCEPT lname FORMAT A10 DEFAULT 'Joy' PROMPT 'Enter Last Name [Joy]:' HIDE
The FORMAT option
specifies that the value for lname
is alphanumeric
and 10 characters in length. The DEFAULT used
if the user does not provide a value when prompted and instead hits
the ENTER key is JOY
. The prompt at the command line
when the ACCEPT command is issued is as defined: Enter
Last Name [JOY]:
. In this case the default is included as
part of the prompt. The HIDE option does not
echo what the user enters as the value for lname
on
the console.
The following example shows the ACCEPT command being used in a CLPPlus script file and the different methods in which a value can be assigned to the defined variable. Consider the following script named average_salary.sql which finds the average salary of an employee in the given department:
ACCEPT dept_id PROMPT "Enter Department ID code : "
SELECT AVG(salary) FROM employee WHERE workdept = &dept_id;
The script can be called in two different ways, with and without arguments.
SQL> start average_salary 'E21'
Original statement:SELECT AVG(salary) FROM employee WHERE workdept = &dept_id
New statement with substitutions:SELECT AVG(salary) FROM employee WHERE workdept = 'E21'
1
------------------------------------------
47086.66666666666666666666666666667
SQL> start average_salary
Enter Department ID code : 'E21'
Original statement:SELECT AVG(salary) FROM employee WHERE workdept = &dept_id
New statement with substitutions:SELECT AVG(salary) FROM employee WHERE workdept = 'E21'
1
------------------------------------------
47086.66666666666666666666666666667