13.8 ARGUMENT
Syntax
ARGUMENT argument_number [PROMPT text] [DEFAULT text] [HIDE]
Customizes the default input prompt text and assigns default values to the parameters when arguments are not passed while executing the script.
Terms
argument_number
Refers to the argument position.
PROMPT
Refers to the customized text for the user input.
DEFAULT
Set the default value for the parameter when input value is not provided when prompted or not pass in as an argument to the script.
HIDE
Masks the input value. It applies to the PROMPT
option.
Usage Notes
- Passed as a script argument
- Set using the
DEFINE
command - Set using the
COLUMN_OLD
and theNEW_VALUE
variables
PROMPT
and the DEFAULT
options work as follows:
- When the
PROMPT
text is specified, the text is displayed instead of the default prompt text. - When the
PROMPT
text is specified with theHIDE
option, you are prompted for input, but the input value is not displayed. - When both the
PROMPT
and theDEFAULT
options are set but the user does not provide a value when prompted, the default value is used. For example, if the user presses the Enter key without specifying a value. - When the
DEFAULT
option is set and thePROMPT
option is not set, the default value is used. In this case, the user is not prompted for input. - When the
PROMPT
option is set and the argument value is not passed to the script, a customized input prompt text is displayed instead of the default prompt text. - When an argument is passed to the script, its value is used. In this case, SQL*Plus neither prompts for input nor uses the default value.
Examples
If the script test1.sql contains:
ARGUMENT 1 PROMPT "Enter value for Arg1:"
DEFINE arg1 = '&1';
SELECT ENAME FROM EMP WHERE EMPNO LIKE '&arg1';
When you execute this script without providing an argument, it will prompt for input. You can then enter a value, as shown in the following example:
@test1.sql
Enter value for Arg1: 7499
The script displays the following output:
ENAME
------
ALLEN
ARGUMENT 1 PROMPT "Enter value for Arg1: " default "%"
DEFINE arg1='&1';
SELECT ENAME FROM EMP WHERE EMPNO LIKE '&arg1';
@test2.sql
Enter value for Arg1:
The default input value (%
) is used. The script displays the following output:
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
ARGUMENT 1 PROMPT "Enter value for Arg1: "
DEFINE arg1='&1';
SELECT ENAME FROM EMP WHERE EMPNO LIKE '&arg1';
When you execute this script without providing an argument, it will prompt for input. Press the ENTER key without entering any input value:
@test3.sql
Enter value for Arg1:
The script displays the following output:
No rows selected.
ARGUMENT 1 DEFAULT "7499"
DEFINE arg1 = '&1'
SELECT ENAME FROM EMP WHERE EMPNO LIKE'&arg1';
@test4.sql
The script displays the following output:ENAME
----------
ALLEN
@test4.sql 7521
The script displays the following output:ENAME
----------
WARD
ARGUMENT 1 PROMPT "enter value for Arg1: " default '%' hide
DEFINE arg1 = '&1';
SELECT ENAME FROM EMP WHERE EMPNO LIKE '&1';
When you execute this script without providing an argument, it will prompt for input. Since the HIDE
option is specified, the value that you entered is masked:
@test5.sql
Enter value for Arg1: ****
The script displays the following output:
ENAME
----------
WARD