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

A prompt is displayed when a parameter has not been defined using any of the following:

Passed as a script argument
Set using the DEFINE command
Set using the COLUMN_OLD and the NEW_VALUE variables

When executing a script, the 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 the HIDE option, you are prompted for input, but the input value is not displayed.
When both the PROMPT and the DEFAULT 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 the PROMPT 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

If the script test2.sql contains:

ARGUMENT 1 PROMPT "Enter value for Arg1: " default "%"
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:

@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.

If the script test3.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. Press the ENTER key without entering any input value:

@test3.sql
Enter value for Arg1:

The script displays the following output:

No rows selected.

If the script test4.sql contains:

ARGUMENT 1 DEFAULT "7499"
DEFINE arg1 = '&1'
SELECT ENAME FROM EMP WHERE EMPNO LIKE'&arg1';

When you execute this script without providing an argument, the default value is used:

@test4.sql

The script displays the following output:

ENAME
----------
ALLEN

When you execute this script with an argument, the argument value is used:

@test4.sql 7521

The script displays the following output:

ENAME
----------
WARD

If the script test5.sql contains:

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