Queries you run every day can become commands of their own in SQLcl. ALIAS gives a statement a short name, and bind variables in it become arguments, filled in order when the alias runs. Aliases are saved between sessions, so they become part of your toolkit.
Code for This Guide
The main examples are in the examples/tools folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
alias name=statement with :bind1 and :bind2; name value1 value2 alias list -- show all aliases alias drop name
The statement can be SQL or a PL/SQL block, and may span lines.
A One-Argument Alias
Example:
alias cancelled=select flight_no from flights where status = 'CANCELLED' and route_id = :r; cancelled 1
Output:
Alias cancelled added FLIGHT_NO ____________ NM101 NM101
CANCELLED lists the cancelled flights of a route; the argument 1 fills :r.
Two Arguments, Then Drop
Example:
-- an alias with two binds, filled in order
alias route_count=select count(*) as routes from routes
where origin = :o and distance_km > :km;
route_count DXB 5000
alias drop route_countOutput:
Alias route_count added
ROUTES
_________
14
Alias route_count droppedROUTE_COUNT takes an origin and a minimum distance: 14 routes from Dubai are longer than 5,000 km. ALIAS DROP removes it.
Things to Know
- Aliases are saved in your SQLcl settings and are available in every new session.
- SQLcl comes with built-in aliases, such as tables and sessions; ALIAS LIST shows them all.
- Choose alias names that do not clash with SQLcl commands or other aliases.
Related Guides
Conclusion
SQLcl aliases turn frequent statements into named commands with arguments. Define them with ALIAS name=statement, run them with values for their bind variables, and keep or share them across sessions.
