In the last post we covered the basics of connecting to a database with SQLcl.
If you’re familiar with Unix, you’ll be familiar with the history command. SQLcl also has a history command and once you start to use it you’ll wonder why sqlplus didn’t have one added years ago (how many times have you mistyped a select statement?).
Once you’ve connected to a database, typing ‘help’ will list the available commands, and you can see the alias command listed.
The general format of the command is
ALIAS ------ alias [<name>=<SQL statement>;| LOAD [<filename>]|SAVE [<filename>] | LIST [<NAME>] | DROP <name> | DESC <name> <Description String>]
Typing alias by itself will list any previously defined aliases
SQL> alias locks sessions tables tables2 SQL>
You can see I have some aliases already defined, but lets create a new one. One thing I commonly do on remote databases is check what the server time(zone) is. I frequently find myself typing the command –
select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual
so, let’s create an alias for that –
SQL> alias time=select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual; SQL>
Now we just need to type ‘time’ and it will run the full command –
SQL> time TO_CHAR(SYSDATE,'DD ------------------- 16/06/2015 19:43:55
Such a simple thing, but so incredibly useful.
I also often find myself returning to a SQLPlus session and wondering which user I’m logged in as – now with SQLcl I can easily create an alias (called whoami to simulate the Unix command), like this –
SQL> alias whoami=select user from dual; SQL> whoami USER ------------------------------ JES
If you want these aliases to persist between database connections, then I can save my current aliases like this –
SQL> alias save aliases.sqlcl ALIAS-007 - Aliases saved to aliases.sqlcl
and then reload them in a future session
SQL> alias load aliases.sqlcl Aliases Loaded
it’s worth noticing that if you examine the file, it is stored in XML format –
<?xml version = '1.0' encoding = 'UTF-8'?> <aliases> <alias name="whoami"> <description/> <queries> <query> <sql><![CDATA[select user from dual]]></sql> </query> </queries> </alias> <alias name="sel"> <description/> <queries> <query> <sql><![CDATA[select :one]]></sql> </query> </queries> </alias> <alias name="time"> <description/> <queries> <query> <sql><![CDATA[select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual]]></sql> </query> </queries> </alias> </aliases>
I can remove any aliases I want using the drop command –
SQL> alias drop whoami Alias whoami dropped
The (very) cool thing is that you’re not just limited to SQL aliases, you can alias PL/SQL too.