DBA Hub

📋Steps in this guide1/5

ARGUMENT Command in SQL*Plus 23ai/26ai and SQLcl 22.4

The ARGUMENT command in SQL*Plus 23ai/26ai 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 he echoed to the screen.

oracle 23configurationintermediate
by OracleDba
13 views
1

PROMPT

The option of the 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. Let's breakdown what we are doing here. - We use so we don't display old and new values for the argument. - We use the command to define the prompt text for argument number 1. - We use the argument in a query to display the value. - We use to make sure the current value of the argument is not reused. In SQLcl we could use to achieve this, but using 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
set verify off
argument 1 prompt "Enter a value for the first argument:"

column arguments format a10

select '&1' as arguments;

undefine 1

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 keyword to the 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 keyword. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
set verify off
argument 1 prompt "Enter a value for the first argument:" hide

column arguments format a10

select '&1' as arguments;

undefine 1

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
set verify off
argument 1 default "banana"

column arguments format a10

select '&1' as arguments;

undefine 1

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 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. We test the amended script and it performs as expected.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
set verify off
argument 1 prompt "Enter a value for arg1:"
define arg1 = '&1';

column arguments format a10

select '&arg1' as arguments;

undefine 1

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. We test the amended script and it performs as expected. For more information see: - ARGUMENT (SQL*Plus) - Defining Parameters in SQLcl Scripts Using the ARGUMENT Command (SQLcl) - SQLcl : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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

SQL> @test.sql
Enter a value for var1:banana

ARGUMENTS
----------
banana

1 row selected.

SQL> @test.sql apple

ARGUMENTS
----------
apple

1 row selected.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!