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.

If loss of significant digits occurs due to overflow of the format settings, the # character is displayed.

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.

When called with arguments, the variable is assigned the value of the argument passed at the time of invocation:

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

When called without arguments, the user interaction is required to assign a value to the variable:

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