Tag Archives: sqlcl

OGB Appreciation Day : SQLcl

Picture from Oracle.com

I am really glad to participate in #ThanksOGB this year. The 2017 edition (called #ThanksODC at that time) was actually the starting point of my blogging journey.

This year, I will not surprisingly talk about a Command Line Interface again : Oracle SQL Developer Command Line, as known as SQLcl.

What is it ?

Simply put :

SQLcl is a command-line interface for Oracle Database that combines the power of SQL*Plus and SQL Developer

Or : it is like SQL*Plus, but much better ;-). I could not live without it. OK, I work less efficiently without it.

Of course I cannot explain all the powerful features of this great tool in this blog post. Instead, I’ll give you a short list of very interesting ones, along with relevant documentation or blog posts (the Internet is full of excellent resources on SQLcl) :

Why can’t I live without it ?

Here are some reasons :

  • The format option lets you format the output in many different ways, as described in this blog post by Kris Rice. In particular, I made ansiconsole my default using login.sql file. Judge for yourself : connect to your favourite database with SQLcl, type set sqlformat ansiconsole, run a query and let the magic happen !
  • Custom aliases are easy to create. An old blog post I wrote contains some examples (and you can also define them with login.sql). There is also a lot of already existing shortcuts to make your life easier : APEX to list APEX apps, CTAS for create table as select, DDL to get an object DDL …
  • Simple but useful, auto-completion of keywords works with tab !
  • One feature that I haven’t tested yet but looks promising is the integration of Liquibase starting from version 19.2. Here is a blog post by Jeff Smith and the official documentation regarding this feature.
  • And last but not least, emojis can be used as a delimiter \o/

I hope you are now convinced that SQLcl is your new best friend ! And if you want to learn new tricks regularly, follow @oraclesqlcl on Twitter !

SQLcl, login.sql, format and alias


(Picture from Oracle.com)

There are (more than) two things that I love with SQLcl : formatting and aliases.

I work with several Grid Infrastructure clusters, hosting hundreds of databases accross multiple nodes. I am very often typing the same queries, this is where SQLcl comes in handy. You can use a login.sql file with SQLcl, just like you would use it with SQL*Plus.
After some tests, I found out that SQLcl looks for a login.sql file in directory and subdirectories pointed by the environment variable SQLPATH.

Continue reading