1. User's Guide
  2. Using Oracle SQLcl
  3. Alphabetic List of SQLcl Commands
  4. Defining Parameters in SQLcl Scripts Using the ARGUMENT Command

2.1.1 Defining Parameters in SQLcl Scripts Using the ARGUMENT Command

The ARGUMENT command enables you to define SQL*Plus parameters that are passed to SQLcl scripts. This command adds a DEFINE for the parameter if the parameter does not exist.

Parameters are passed by position to scripts where they are assigned a number starting with one for each position. Therefore, to supply a value for parameter 1, use argument 1 [options]; for parameter 2, use argument 2 [options], and so on. See Passing Parameters through the START Command in the Oracle SQL*Plus User's Guide for more information.

Syntax

argument|arg OPTIONS

Use the SET PARAMETERPOLICY command to control parameter retention. The parameter retention applies to all parameters whether defined using this command or not.

When SET PARAMETERPOLICY is SHARE (default), defined variables retain their values until you:

  • Enter a new DEFINE command referencing the variable.
  • Enter an UNDEFINE command referencing the variable.
  • Enter an ACCEPT command referencing the variable.
  • Reference the variable in the NEW_VALUE or OLD_VALUE clause of a COLUMN command and then reference the column in a SELECT command.
  • Exit SQLcl.

When SET PARAMETERPOLICY is ISOLATE:

  • Parameter settings are saved and undefined at the start of the script.
  • On return from the script, the saved parameters are restored.
  • Parameter settings in called scripts do not affect the containing script.

The SET PARAMETERPOLICY command must be specified before the script is called. If SET PARAMETERPOLICY is set to ISOLATE within a script, parameters are removed on return. Parameters cannot be reset as the values were not saved at the start of the script.

Options

Option Description
Required
arg_num The position of the parameter relative to 1.
action {default|prompt}

The argument action to take when a parameter is not already defined.

  • prompt: Prompts the user for the parameter value and sets it.
  • default: Sets the default value.
action_value The value to specify for the parameter or the string for prompting the user for the value. Values containing spaces must be enclosed in double quotes.
Optional
comment {comment}

Associate a comment with the parameter.

{comment} - Associate the comment value specified with the parameter.
comment_value

The comment to associate with the parameter. Values containing spaces must be enclosed in double quotes.

Examples

Example 1 - The following example uses the ARGUMENT command to define two parameters in a script (script.sql). 

script.sql:
prompt 'Give value for 1 or prompt for it: &1'
prompt 'Give value for 99 or prompt for it: &99'

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> arg 99 default 99_set
/* 99 shared with script.sql */

SQL> @script.sql
Enter value for 1: x
'Give value for 1 or prompt for it: x'
'Give value for 99 or prompt for it: 99_set'
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Example 2 - The following example illustrates how the SET PARAMETERPOLICY ISOLATE is used to redefine parameters every time the script is called. 

SQL> set parameterpolicy isolate
SQL> arg 99 default 99_set
/* 99 is not passed to the script, so prompted for */

SQL> @script.sql
Enter value for 1: x2
'Give value for 1 or prompt for it: x2'
Enter value for 99: x99
'Give value for 99 or prompt for it: x99'
/* 99 restored - set back to the original value when script.sql finishes */

SQL> define 99
DEFINE 99              = "99_set" (CHAR)