ARGUMENT Command in SQL*Plus 23ai and SQLcl 22.4

The ARGUMENT command in SQL*Plus 23ai and SQLcl 22.4 allows us to manage arguments passed to a script. This includes the prompt or default value if an argument is missing, and whether the user input from a prompt should be echoed to the screen.

Remember, SQLcl is shipped independently of the database, so we can use the ARGUMENT command against any version of the database from 11.2 onward.

1. PROMPT

The PROMPT option of the ARGUMENT command allows us set the prompt text displayed if the argument is missing from the command line. Create a file called "test.sql" with the following contents.

set verify off

argument 1 prompt "Enter a value for the first argument:"

column arguments format a10

select '&1' as arguments;

undefine 1

Let's breakdown what we are doing here.

◉ We use SET VERIFY OFF so we don't display old and new values for the argument.

◉ We use the ARGUMENT command to define the prompt text for argument number 1.

◉ We use the argument in a query to display the value.

◉ We use UNDEFINE to make sure the current value of the argument is not reused. In SQLcl we could use SET PARAMETERPOLICY ISOLATE to achieve this, but using UNDEFINE works for both tools.

We test the script. In the first example we are prompted and provide the value "banana". In the second example we are prompted and provide the value "apple". In the third example we provide the value "pear" on the command line, and we are not prompted to enter a value.

SQL> @test.sql

Enter a value for the first argument:banana

ARGUMENTS

----------

banana

SQL> @test.sql

Enter a value for the first argument:apple

ARGUMENTS

----------

apple

SQL> @test.sql pear

ARGUMENTS

----------

pear

1 row selected.

SQL>

2. PROMPT and HIDE (SQL*Plus Only)

Adding the HIDE keyword to the ARGUMENT command means the text we enter is no longer echoed to the screen. This is useful when entering passwords. We amend the previous script to add the HIDE keyword.

set verify off

argument 1 prompt "Enter a value for the first argument:" hide

column arguments format a10

select '&1' as arguments;

undefine 1

We test the amended script. In the first example we are prompted and provide the value "secret". Notice this is not echoed to the screen. In the second example we provide the value "secret2" on the command line, and we are not prompted to enter a value.

SQL> @test.sql

Enter a value for the first argument:

ARGUMENTS

----------

secret

1 row selected.

SQL> @test.sql secret2

ARGUMENTS

----------

secret2

1 row selected.

SQL>

3. DEFAULT

Not surprisingly the DEFAULT keyword allows us to define a default value to use if an argument is not specified. We amend the previous script to assign a default value.

set verify off

argument 1 default "banana"

column arguments format a10

select '&1' as arguments;

undefine 1

We test the amended script. If we don't provide a command line argument the default value of "banana" is used. If we provide the command line argument, it is used.

SQL> @test.sql

ARGUMENTS

----------

banana

1 row selected.

SQL> @test.sql apple

ARGUMENTS

----------

apple

1 row selected.

SQL>

4. ARGUMENT with DEFINE

In the previous examples we have used the arguments directly, but we could use them in conjunction with a DEFINE command. We amend the test script giving it the following contents. In this example we have used the first argument to define "arg1", which we use the in the subsequent query.

set verify off

argument 1 prompt "Enter a value for arg1:"

define arg1 = '&1';

column arguments format a10

select '&arg1' as arguments;

undefine 1

We test the amended script and it performs as expected.

SQL> @test.sql

Enter a value for arg1:banana

ARGUMENTS

----------

banana

1 row selected.

SQL> @test.sql apple

ARGUMENTS

----------

apple

1 row selected.

SQL>

5. ARGUMENT with VARIABLE

We can also use arguments with variables defined in SQL*Plus and SQLcl. We amend the test script giving it the following contents. In this example we have defined a variable called "var1" and set it to the value if the first argument. We use the variable in the subsequent query.

variable var1 varchar2(10);

set verify off

argument 1 prompt "Enter a value for var1:"

set feedback off

exec :var1:= '&1';

set feedback on

column arguments format a10

select :var1 as arguments;

undefine 1

We test the amended script and it performs as expected.

SQL> @test.sql

Enter a value for var1:banana

ARGUMENTS

----------

banana

1 row selected.

SQL> @test.sql apple

ARGUMENTS

----------

apple

1 row selected.

SQL>

Source: oracle-base.com