{"id":73141,"date":"2015-11-30T15:31:25","date_gmt":"2015-11-30T15:31:25","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/sqlcl-the-new-challenger-for-the-sql-plus-crown\/"},"modified":"2021-07-14T13:07:19","modified_gmt":"2021-07-14T13:07:19","slug":"sqlcl-the-new-challenger-for-the-sql-plus-crown","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/sqlcl-the-new-challenger-for-the-sql-plus-crown\/","title":{"rendered":"SQLcl: The new challenger for the SQL Plus crown"},"content":{"rendered":"

I\u2019m that grumpy old guy who comes to your party and sits in a corner with a face like a dog\u2019s backside. You know, that guy who hates everything new: Adele? She\u2019s no Whitney Houston! Bluray? It\u2019s not as good as Betamax! TOAD? It\u2019s not as good as SQL Plus!<\/em><\/p>\n

I\u2019m exaggerating – but only a little. Unless your work involves running lots of sql scripts or you\u2019re 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\u2019m not a complete idiot – but I have a huge amount of affection for SQL Plus, and yes, I felt like I\u2019d lost a friend when sqlplusw.exe died with 11g.<\/p>\n

Oracle are now trying to fill that hole in our hearts with a new product, SQLcl. (The cl stands for \u201ccommand line\u201d.) 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<\/a>). And I\u2019ve been living with it these past few days.<\/p>\n

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\u2019s 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\u2019t this have a cool name too?<\/p>\n

\"SQL<\/p>\n

But that\u2019s 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\u2019ll 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.<\/p>\n

\"SQL
\nAt 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?<\/p>\n

Here are a few commands that I\u2019ve been playing with.<\/p>\n

Alias<\/h5>\n

Alias is SQLcl\u2019s analog of SQL Developer\u2019s Snippets, which, in case you\u2019re 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\u2019s how you use it:<\/p>\n

If, for example, you\u2019re constantly running a query to list the employees in various departments, you might create the following alias:<\/p>\n

alias get_emps=SELECT emp.empno, emp.ename, emp.sal\r\nFROM emp, dept\r\nWHERE emp.deptno = dept.deptno\r\nAND dept.dname = :dept\r\nORDER BY 1;<\/pre>\n
That way, if you need to list all the employees in the ACCOUNTING department, all you have to do is type:<\/p>\n
SQL> get_emps ACCOUNTING;<\/pre>\n
The reason we can pass in \u2018ACCOUNTING\u2019 is that we\u2019d set up our alias to accept the department name as a parameter ( and dname = :dept<\/i>).<\/p>\n
Two things I had to learn the hard way using aliases are that, firstly, their names are case-sensitive and, secondly, SQLcl doesn\u2019t 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\u2019t expect to be able use it tomorrow.<\/p>\n
Unless\u2026<\/p>\n
LOGIN.SQL<\/h5>\n
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.<\/p>\n
In my SQLcl login.sql I now also create my aliases. This way they’re always available to me.<\/p>\n
SQLFORMAT<\/h5>\n
Something else that I include in my login.sql file is a call setting up the sqlformat.<\/p>\n
With a call to sqlformat, SQLcl allows you configure how your queries are outputted to the screen.<\/p>\n
If you have any experience with SQL Plus, you\u2019ll 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:<\/p>\n
set sqlformat ansiconsole<\/pre>\n
\"Select
\n\"Select<\/p>\n
It works so brilliantly that I\u2019m surprised it isn\u2019t the default setting.<\/p>\n
Many of the other sqlformat settings, however, are just as useful: csv, insert, xml, json.\u00a0I sometimes need to copy data from our Live environment to a Test database; setting the sqlformat to insert<\/i> makes this the easiest thing in the world.<\/p>\n
BRIDGE<\/h5>\n
Or should that be the second<\/i> easiest thing in the world? Because SQLcl\u2019s new BRIDGE command seeks to make it even easier.<\/p>\n
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. <\/span><\/p>\n
The syntax is as follows:<\/span><\/p>\n
BRIDGE <targetTableName> as \"<jdbcURL>\"<<sqlQuery>>;<\/pre>\n
\"\"<\/p>\n
CTAS<\/h5>\n
Obviously, we don\u2019t always want to cross database borders when copying tables; often we just want to copy a table from the same schema. Personally, I\u2019ve 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:<\/p>\n
ctas <table> <new_table>\r\n<\/pre>\n
ctas emp emp_copy;\r\n<\/pre>\n
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.<\/p>\n
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\u2019s ddl.<\/p>\n
ddl emp_copy;\r\n<\/pre>\n
INFO \/ INFO+<\/h5>\n
However, you are probably better off using the new INFO command (it’s not exactly <\/em>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\u2019s columns, Info will also tell you what indexes it has, its constraints and when it was last analysed.<\/p>\n
And if that isn\u2019t enough information for you, you can call info+<\/i> and SQLcl will also furnish you with stats information. Which, I guess, makes info+ the equivalent of desc on Red Bull and<\/i> Haribo.<\/p>\n
\"SQLCI<\/p>\n
Additionally, unlike with DESC, you can use INFO to get the spec of an individual packaged procedure or function.<\/p>\n
info package.function;<\/pre>\n
HELP<\/h5>\n
In truth, I\u2019m 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.<\/p>\n
Using SQLcl<\/h5>\n
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.<\/p>\n
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\u2019ll auto-complete the name for you. And if more than one table name matches your search term, it\u2019ll list them out for you at the bottom of the screen.<\/p>\n
More impressive still is the HISTORY command. SQLcl retains the last 100 commands that have been executed against the current client, whether they\u2019re sql, pl\/sql or SQL Plus or SQLcl commands like info+.<\/p>\n
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<\/i> 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.<\/p>\n
Another new SQLcl command – CD – looks really mundane; Change Directory<\/i>, what\u2019s 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.<\/p>\n
Conclusion<\/h5>\n
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.<\/p>\n
Will that be the fate of SQLcl? I think not; I hope<\/i> 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.<\/p>\n
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?<\/p>\n
And the answer is: yes.<\/p>\n
References:<\/em><\/span>
\nThat Jeff Smith<\/a> (Oracle SQL Developer product manager)
\nThe Anti-Kyte:SQLCL \u2013 The New SQL*Plus<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
I\u2019m that grumpy old guy who comes to your party and sits in a corner with a face like a dog\u2019s backside. You know, that guy who hates everything new: Adele? She\u2019s no Whitney Houston! Bluray? It\u2019s not as good as Betamax! TOAD? It\u2019s not as good as SQL Plus! I\u2019m exaggerating – but only a little. Unless your work…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48511,48512],"coauthors":[],"class_list":["post-73141","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-sqlcl","tag-sqlplus"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73141"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73141\/revisions"}],"predecessor-version":[{"id":91637,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73141\/revisions\/91637"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73141"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}