Why Aren’t You Using SQLcl?

Does this look familiar?

When you make a typo and realize "arrow up" doesn't work

All over Linux you can use arrow up to get the latest command and edit it. But not in SQL*Plus! Something I used to forget many times a day…

I was recently in a presentation where the presenter had a really bad day – typing-wise, that is. He kept making typos all the time and he always ended up copying the text over in an editor, do the changes, and paste back. In the end you could really feel his frustrations (even though it was a virtual presentation). I really felt with that guy, probably because it could just as well be me. Oh, that feeling you get after typing a lengthy SQL only to find a stupid typo!!!

I gave up on my typing skills a long time ago and I am now happy user of SQLcl. So when I make a typo I just do this:

Edit your statements in SQLcl

But I still meet a lot of people that are stuck in the last millenium aka SQL*Plus. So, the big question is:

Why aren’t you using SQLcl?

With the alias function in SQLcl I can do this:

Create your own alias to quickly execute commands

With tab completion I can do this:

Tab completion - works on tables, views, keywords, columns ...

And look at the beautifully formatted query result I get in SQLcl using set sqlformat ansiconsole:

SQLcl can format query result in a much better way

Still not convinced? Check out this post from fellow Product Manager Jeff Smith in which he sells you SQLcl in 1 minute.

May this be the day where you did your last SQL*Plus typo!

My Aliases

For your reference here are the aliases that I used in the demo:

pdbs = select con_id, name, open_mode, restricted from v$pdbs order by con_id
sc = exec execute immediate 'alter session set container='||:one
scr = alter session set container=CDB$ROOT
ts = select p.con_id, p.name, vt.name, ct.encrypted, ct.status, ct.contents, ct.compress_for from v$tablespace vt, v$pdbs p, cdb_tablespaces ct where vt.con_id=p.con_id(+)  and vt.name = ct.tablespace_name(+) and vt.con_id = ct.con_id (+) order by vt.con_id, vt.name

2 thoughts on “Why Aren’t You Using SQLcl?

  1. > Linux you can use arrow up to get the latest command and edit it.
    Why dont you use “rlwrap” ?
    $ rlwrap sqlplus / as sysdba
    arrow up works …
    and most important: “Ctrl-R” works to seach over commands executed in the past (exactly like in bash)
    SQLcl is a cool tool … but I have not figured out about how to search over history and execute the result ?!
    “Ctrl-R” is probably most used key combination in sqlplus and missing alternative in SQLcl is major blocker to migrate fully.


  2. Hi Marek,

    Thank you for your comment. You’re right that you can install rlwrap, and it helps a lot. But even with rlwrap there are so many things that you are missing out on. I really like the idea or CTRL + R. You should reach out to Jeff Smith on Twitter (@thatjeffsmith) and provide the idea. He is the PM for SQLcl.



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 )

Connecting to %s