How to Share SQLcl Aliases

SQLcl supports aliases which can save a lot of time when running the same queries or scripts by not having to type them each time. A simple example of an aliases is to show the current date. Instead of having to type select sysdate from dual; each time you could do this:

-- Only do this once
alias today=
  select sysdate
  from dual
;

-- To run it just type: today

SQL> today
     SYSDATE
____________
11-MAR-20

If you are new to SQLcl aliases or the concept of login.sql (which runs each time SQLcl is launched) I suggest reading the following articles before continuing with this article.

• Intro to aliases by Jeff Smith
• {% post_link sqlcl-and-loginsql login.sql %}

If you use multiple machines or want to share aliases you may think you're limited to sending code snippets to people or blogging about them. Luckily SQLcl can reference files stored on the web! I've posted some of my SQLcl aliases on Github here. To load them just run @https://raw.githubusercontent.com/martindsouza/oracle-sqlcl/master/aliases.sql in SQLcl. If you want to constantly get an updated version of these aliases just add the line above to your login.sql file. I'm currently working on fine tuning my SQLcl aliases and will keep Github file up to date.

One example of a alias I have is called invalid which lists all invalid objects in your schema. Example:

SQL> invalid
                OBJECT_NAME     OBJECT_TYPE
___________________________ _______________
BIU_OOW_DEMO_EVENT_LOG      TRIGGER
BI_OOW_DEMO_HIST_GEN_LOG    TRIGGER
EBA_DEMO_IG_TEXT_PKG        PACKAGE BODY
OOW_DEMO_GEN_DATA_PKG       PACKAGE BODY
OOW_DEMO_PREFERENCES_BIU    TRIGGER
PKG_ORDS                    PACKAGE BODY
PKG_ORDS                    PACKAGE

Some closing thoughts:

• If referencing other people's aliases (via the web / .sql file) be careful to either review the file or ensure it comes from a trusted source as malicious code could be easily added.
• Instead of having a lot of content in your login.sql file you can keep it to one line (referencing a file on Github for example) and keep the Github file updated with everything. This way if you change machines or use multiple machines they'll all have the same experience.