Enhancing Script Usability with the ARGUMENT Command in SQL*Plus 23ai and SQLcl 22.4
Oracle December 23, 2024The flexible ARGUMENT command is a feature in Oracle's SQLPlus 23ai and SQLcl 22.4 that makes managing script arguments easier. This command improves the usability of scripts for developers by offering secure input processing, default values, and customizable prompts. With SQLPlus or SQLcl, the ARGUMENT command offers efficiency and flexibility.
The Argument Command's Primary Features
1. Requesting Input
When a script argument is missing, developers can interactively request input using the PROMPT option. Here's a basic illustration:
set verify off argument 1 prompt "Enter your preferred city:" column city format a15 select '&1' as city; undefine 1 |
When executed, the script prompts the user for input:
SQL> @location.sql Enter your preferred city: Tokyo CITY --------------- Tokyo |
2. Hiding Sensitive Input (SQL*Plus Only)
For confidential data such as API keys, the HIDE option ensures input remains concealed:
set verify off argument 1 prompt "Enter your API key:" hide column api_key format a20 select '&1' as api_key; undefine 1 |
Output remains secure:
SQL> @apikey.sql Enter your API key: API_KEY -------------------- ******** |
3. Using Default Values
The DEFAULT keyword lets scripts fall back on predefined values when no input is provided:
set verify off argument 1 default "guest" column username format a10 select '&1' as username; undefine 1 |
If no input is given, the script uses the default:
SQL> @login.sql USERNAME ---------- guest |
4. Dynamic Assignments with DEFINE
Combine ARGUMENT with the DEFINE command to dynamically assign user inputs:
set verify off argument 1 prompt "Enter your role:" define role = '&1'; column user_role format a15 select '&role' as user_role; undefine 1 |
5. Working with Variables
Use ARGUMENT with SQL*Plus variables for advanced scenarios:
variable dept_name varchar2(20); set verify off argument 1 prompt "Enter department name:" set feedback off exec :dept_name := '&1'; set feedback on column department format a20 select :dept_name as department; undefine 1 |
SQLcl-Specific Enhancements
The ARGUMENT command is extended by SQLcl, which offers simpler syntax and dynamic settings. It is a strong stand-alone tool because it works with Oracle Database versions 11.2 and higher.
For example, define multiple arguments with defaults:
argument 1 default "North" argument 2 default "Sales" select '&1' as region, '&2' as division from dual; |
Output:
REGION DIVISION ------------- ------------- North Sales |
Conclusion
In SQL*Plus 23ai and SQLcl 22.4, the ARGUMENT command provides an advanced way to manage script arguments. For database experts, its ability to manage prompts, secure inputs, default settings, and integrations with DEFINE and VARIABLE commands makes it an essential tool. This functionality transforms the way Oracle scripting is done by enabling developers to write interactive and flexible scripts.
