I’m that grumpy old guy who comes to your party and sits in a corner with a face like a dog’s backside. You know, that guy who hates everything new: Adele? She’s no Whitney Houston! Bluray? It’s not as good as Betamax! TOAD? It’s not as good as SQL Plus!
I’m exaggerating – but only a little. Unless your work involves running lots of sql scripts or you’re stuck on some server terminal, no one really needs to use SQL Plus these days – and, perhaps, not even then. However, every software team has its stubborn dinosaur who, like me, loves SQL Plus and still uses it at every opportunity. Obviously I use TOAD and SQL Developer for most things – I’m not a complete idiot – but I have a huge amount of affection for SQL Plus, and yes, I felt like I’d lost a friend when sqlplusw.exe died with 11g.
Oracle are now trying to fill that hole in our hearts with a new product, SQLcl. (The cl stands for “command line”.) It is a sql command line interface that is built around the SQL Developer script engine. It is new – so new that it is still currently in the Early Adopter stage – and lightweight – only around 12mb (download here). And I’ve been living with it these past few days.
My first thought is that I do not like the name, SQLcl. It was previously called SDSql, which is worse, and apparently SQL*Plus++, which sounds like it’s trying too hard to be cool. I remember ten years ago when SQL Developer was in its EA stage; it was called Project Raptor. Why can’t this have a cool name too?
But that’s a minor quibble. The download is a zipped tool (bin\sql.exe) that requires JRE to run (unsurprisingly, since you need Java to run SQL Developer too). It’ll happily read your tnsnames.ora to connect to your databases (if you have the TNS_ADMIN environment variable); however, you can also use the EZConnect syntax and simply pass in a host:port/service_name combination with your username and password.
At first glance, SQLcl looks very much like SQL Plus – similar enough to make you wonder why bother. What extras does it bring to the party?
Here are a few commands that I’ve been playing with.
Alias is SQLcl’s analog of SQL Developer’s Snippets, which, in case you’re not familiar with it, is functionality that allows you save snatches of sql or pl/sql that you use regularly and call them up easily. I never remember to use Snippets in SQL Developer, but I really love Alias in SQLcl. Here’s how you use it:
If, for example, you’re constantly running a query to list the employees in various departments, you might create the following alias:
alias get_emps=SELECT emp.empno, emp.ename, emp.sal FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.dname = :dept ORDER BY 1;
That way, if you need to list all the employees in the ACCOUNTING department, all you have to do is type:
SQL> get_emps ACCOUNTING;
The reason we can pass in ‘ACCOUNTING’ is that we’d set up our alias to accept the department name as a parameter ( and dname = :dept).
Two things I had to learn the hard way using aliases are that, firstly, their names are case-sensitive and, secondly, SQLcl doesn’t save them for you. So if you write a long, complicated alias today and then, feeling all happy with yourself, you shut down your computer and go home to have a beer, you shouldn’t expect to be able use it tomorrow.
Fortunately, like SQL Plus, SQLcl allows you to automatically run a set of scripts at start up. I use my login.sql to set up little things like my sql prompt, and to set timing and serveroutput on.
In my SQLcl login.sql I now also create my aliases. This way they’re always available to me.
Something else that I include in my login.sql file is a call setting up the sqlformat.
With a call to sqlformat, SQLcl allows you configure how your queries are outputted to the screen.
If you have any experience with SQL Plus, you’ll know that if you select too many columns in a query, your resultset will look a jumbled mess. However, in SQLcl, you can ask for your results to be formatted to best fit your console:
set sqlformat ansiconsole
It works so brilliantly that I’m surprised it isn’t the default setting.
Many of the other sqlformat settings, however, are just as useful: csv, insert, xml, json. I sometimes need to copy data from our Live environment to a Test database; setting the sqlformat to insert makes this the easiest thing in the world.
Or should that be the second easiest thing in the world? Because SQLcl’s new BRIDGE command seeks to make it even easier.
Bridge temporarily builds a, um, bridge between schemas in two databases similar to the way that a database link might, and allows you create a table in your schema based on a query run in the other schema.
The syntax is as follows:
BRIDGE <targetTableName> as "<jdbcURL>"<<sqlQuery>>;
Obviously, we don’t always want to cross database borders when copying tables; often we just want to copy a table from the same schema. Personally, I’ve never found typing out Create Table x As Select * from y particularly arduous, but the SQLcl team must not enjoy typing as much as I do. They have included a new CTAS command that makes the whole thing a doddle:
ctas <table> <new_table>
ctas emp emp_copy;
The clever thing about ctas (and I have to admit that I did the SQLcl guys a disservice with my quip about being lazy) is that it does NOT automatically create the table for you, instead it feeds the ddl to the screen and to the buffer. You can then either choose to run it as is, or type edit and make changes to it before executing it.
And then, if you want to be doubly sure that it has created the table correctly, you can always call the new DDL command to output your table’s ddl.
INFO / INFO+
However, you are probably better off using the new INFO command (it’s not exactly new; it’s been part of SQL Developer for yonks, but I’ve always hated the way its results are formatted there). Consider it the humble old DESC command on Red Bull. Instead of just listing a table’s columns, Info will also tell you what indexes it has, its constraints and when it was last analysed.
And if that isn’t enough information for you, you can call info+ and SQLcl will also furnish you with stats information. Which, I guess, makes info+ the equivalent of desc on Red Bull and Haribo.
Additionally, unlike with DESC, you can use INFO to get the spec of an individual packaged procedure or function.
In truth, I’m burying the lead by going on about the various new commands that come with SQLcl. Typing HELP will give you enough information for you to get started.
No, what I find most impressive about SQLcl is how easy it is to use, and the neat little tricks the developers have included to make our lives easier.
Take tab completion, for example. In SQLcl, you can start typing a table name and, if you get bored partway through, you can hit tab and it’ll auto-complete the name for you. And if more than one table name matches your search term, it’ll list them out for you at the bottom of the screen.
More impressive still is the HISTORY command. SQLcl retains the last 100 commands that have been executed against the current client, whether they’re sql, pl/sql or SQL Plus or SQLcl commands like info+.
Typing history will output a numbered list of these commands to the screen. And if you want to run, say, the 13th command in the list? All you need do is type history 13 to load it into the buffer, ready to run. Alternatively, you can flip through the commands using your up arrow as you might do in SQL Plus.
Another new SQLcl command – CD – looks really mundane; Change Directory, what’s the big deal? The big deal is that we were never able to change directories from within SQL Plus before. You had to start the application from within the correct directory or direct it to your scripts with the full path. In SQLcl you can simply cd to the desired directory and be done with it.
SQL Plus is like Stallone in the Rocky movies; old, ugly and punch-drunk, it has outlasted all challengers. It is not a full-featured IDE and has no aspirations to be one; it does simple things and it does them well. Flashy upstarts like iSqlplus tried to do too much and they failed, died and disappeared.
Will that be the fate of SQLcl? I think not; I hope not. It is less than a year old and still a little rough around the edges. However, the developers seem to be constantly improving it, and it benefits from a cross-fertilisation of ideas with SQL Developer.
I only started using it as research for this article, with no thoughts of it replacing SQL Plus permanently. And so the question is, will I continue using it, now that I am typing the final words of the article?
And the answer is: yes.sqlcl, sqlplus