8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
Related articles.
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 useSET PARAMETERPOLICY ISOLATE
to achieve this, but usingUNDEFINE
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>
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>
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>
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>
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>
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...