SQLcl : Going cold turkey from SQL*Plus

I’m a SQL*Plus junkie. I have loads of scripts that do exactly what I want them to do, so for most tasks I am more productive from the command line than using other tools. If I do find something difficult, I just flick across to SQL Developer or Cloud Control. I’m not a snob about it, but I prefer the command line.

A couple of weeks ago I was playing with the ORDS stuff in SQLcl.

That made me think I really should make the switch, but I didn’t. Then I had to dump something out as CSV, and SQLcl makes that really easy.

I forgot the 12.2 SQL*Plus client I was using could also do this, even though I had written about it (here), but the SQLcl formatting is more flexible anyway. 🙂

So after that I decided to try an experiment and go cold turkey from SQL*Plus and only use SQLcl. I think today is the 1 week anniversary. 🙂

The only real problem has been muscle-memory. My hand naturally types “sqlplus” rather than “sql”. It’s going to take a long time to get out of that habit. 🙂

I’m not using all the extra features all the time, so I can’t claim it’s revolutionised my life, but having them at hand is certainly … handy. 🙂

I’ve also started using it on some of my application servers. On some of them I have to check for the presence of the database before starting the application server. Just the standard race condition stuff you get during failover events. I was using the instant client before, but I’ve switched to SQLcl now because it is smaller. That’s especially nice on Docker.

So that’s one week down and I’ve got no plans (yet) to switch back. 🙂



Don’t forget the COPY command in SQL*Plus (and SQLcl)

One of the developers asked me to copy a small table from Live to Dev. In situations like this, my first thought is to use the SQL*Plus COPY command. By the way, this command is also available in SQLcl.

It’s super-easy and has been around forever. Provided you can live with the data type restrictions, it’s a lot less hassle than expdp/impdp, even with the NETWORK_LINK option.

As always, it’s in the documentation, but the SQL*Plus help text tells you how to use it, so you don’t even have to RTFM is you don’t want to. 🙂

SQL> help copy


Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
[(column, column, column, ...)] USING query

where database has the following syntax:

What I ended up doing was something like this.

conn scott@dev
copy from scott@live create scott.emp_20151201 using select * from scott.emp;

Because it’s been around for so long, you rarely see people talk about it, which made me wonder how many newer folks may not have noticed it, hence this blog post. 🙂




During his unconference session at OpenWorld 2007, Lutz Hartmann used rlwrap to give SQL*Plus and RMAN command line history and basic editing functionality. Like the Windows Process Explorer post I wrote recently, this is another example of a gizmo I’ve used in the past then completely forgotten about, so I’m grateful to Lutz for reminding me. To see how I install and configure it click here.



Update: Someone and just told me my rlwrap post is now ranked higher than Howard’s on Google. This is really a “duck and cover” event. 🙁