SQLcl – Using Aliases

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.

One thought on “SQLcl – Using Aliases

  1. Pingback: SQLcl – do REPEAT yourself | Johns Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s