To borrow a well-known saying, One-Day International Cricket is a game played by two sides for 100 overs…and then the Aussies win the World Cup.
Something else that doesn’t seem to change much over time is SQL*Plus. The command line interface to Oracle Databases has been around, in it’s current guise, since 1985.
Whilst there have been some changes here and there, it’s basic functionality has remained largely unchanged over the intervening 30 years.
Now, however, it looks like things are about to change as Oracle lavish some attention on the noble CLI.
You may be wondering how this is in any way relevant in the modern world of GUI development.
Well, there are still some things that you need the command line for.
Interactive connection to a database from a server that’s not running a desktop environment would be one.
More common though, are those jobs that need to run unattended. These will include batch jobs managed by a scheduling tool external to the Oracle RDBMS, such as Autosys, or even good old CRON.
Increasingly, it will also include jobs that are initiated as part of Continuous Integration or Release Management testing.
SQL*Plus for the 21st Century is currently going by the name of SQLCL (SQL Command Line). It has also been known as SDSQL (SQLDeveloper SQL) and even SQL*Plus++ (my personal favourite).
Whilst the currently available versions of SDSQL are very much in the Early Adopter stage, there is enough there to show the direction in which things are moving.
Whilst the decision has been taken to ensure that SDSQL is fully backward-compatible with the current SQL*Plus, some of the new features may well have significant implications in the way that the Oracle CLI is used in the future.
What I’m going to cover here is :
How SDSQL differs “structurally” from SQL*Plus
Improvements in command-line interaction incorporating Linux-like and IDE features, including glorious technicolour!
All of which leaves the ageing geek in me unashamedly excited.
Before I go on, I should take this opportunity to say thanks to Jeff Smith and the team for answering the questions I had about some of the new features.
Download and Installation
The first thing you’ll notice about SDSQL is that, unlike the Oracle Client of which SQL*Plus is a component, it is a single file.
This is a pretty good start as you don’t have to figure out which files you need to download before you get going.
Instead, you simply need to head over to the SQLDeveloper Download Page and download Command Line SDSQL – All Platforms.
You are rewarded with posession of 11MB worth of :
Once you’ve unzipped the tool, go to the bin directory. In my case :
Here you will find the following files :
sql.bat – a Windows batch script
sql – a bash script
sql.exe – a Windows executable
On a Windows client, you can just run sql.exe and be on your way. The bash script provided ultimately executes this command :
In order for SDSQL to run whether on Windows or Linux, you need Java installed. To be more precise, you need a Java Runtime Environmnent (JRE) of version 1.7 or higher.
This in itself is not a major issue. However, it’s worth bearing this dependency in mind if you’re working in a large organization with a separate Server Admin team as you’ll probably need to negotiate some process hurdles to get a suitable JRE onto your servers if there isn’t one already in place.
Database Connections
As SDSQL isn’t part of the client, it does not assume that there is a tnsnames.ora hanging around for it to read (although it will happily read one that’s pointed to by the TNS_ADMIN environment variable).
I think it’s fair to say that the “native” connection method is to use the EZConnect syntax which has the format :
So, connecting to my local XE database interactively can look something like this :
…where my database is on the current machine, the TNS Listener is on port 1526 and the SID is XE.
Of course, having an Oracle Client installed and a TNS entry for my database specified, I can also still do this :
There are other connection methods available. Barry McGillin has an example of connecting via LDAP.
Of course, using the EZConnect syntax all the time, especially when you open another connection from within your session could involve a fair amount of typing. Fortunately, The developers have thought of this…
NET
The NET command allows you to “save network details and assign it a shortcut command”.
For example, to create an alias for the database running on a Developer Day VirtualBox image :
NOTE – you need to terminate the string with a semi-colon at the end of the line.
To test it, we first need to enable NET in our SQLCL session (it’s set to OFF by default) …
So, whilst connected to XE as mike, I can use the NET alias I’ve just created to connect to the Developer Day database :
The Command Line, but not as we know it
When it comes to command-line editing and scrollback, SQL*Plus is, well, a bit basic.
If you’re on Windows, you can scroll back through previous commands. Unfortunately, even this is not a feature on Linux.
There have been efforts to make the SQL*Plus a bit more friendly over the years, notably the RLWRAP project.
If you haven’t discovered the joys of that particular software, you are about to have a new experience at the SQL prompt…
Tab Completion
Type the following…
when you hit the TAB key, the table name is automagically completed for you.
OK, it uppercases the table name to REGIONS but hey, if you’re that worried about code formatting…well, we’ll get to that in a bit.
In the meantime, just marvel at the fact that it’s almost like being at the $ prompt.
Multi-Line Console Editing
If you think that’s good…
I run this and then realise I really should have added a predicate to avoid having to wade through a massive result set ( OK, it’s only 4 rows, but just bear with me here).
Rather than having to fiddle about with the arcane SQL*Plus editing commands, I can simply use the list command to show me what’s currently in the buffer ( i.e. the query I’ve just run) and then hit the up arrow.
I’m now magically transported to the end of the last line of the statement. I can add a new line, simply by hitting RETURN and then add my predicate (NOTE – it’s probably a good idea to hit back arrow, forward arrow if you’re doing this as otherwise SQLCL thinks you want to cycle through the previous commands you’ve issued).
The uppercase REGION_ID indicates that I got this column name using tab completion.
Barry McGillin has a demonstration of this feature here.
HISTORY
Rather than simply remembering the last SQL or PL/SQL that was executed, SQLCL retains a history of the last 100 commands executed. Note that this is the last 100 commands for any user connecting on the current client.
This includes SQL*Plus commands, such as describe.
You can scroll back and forward through the previous commands using the arrow keys – nothing new for Windows clients but it is for Linux.
Alternatively, you can get a listing by typing :
This will give you a numbered listing of the commands in the history.
Incidentally, if you use the built-in SQLCL help command for history, you’ll get this listing :
However, if you try the help option of the history command, you get something slightly different …
The full option appears to simply list all of the commands in the history ( the same as simply issuing the HISTORY command on it’s own).
When we look at some of the other options, it gets a bit interesting :
The USAGE option keeps a count of the number of times a particular command has been executed.
The TIME option shows the execution time for the first execution of a command.
In some circumstances, this could prove handy, especially when you realise that you need to set timing on…just after you’ve kicked off your script.
If you want to cleardown the history…
One point to note – as with it’s Linux counterpart, HISTORY retains any command executed using that client, not simply any command when connected as a particular user. This is something you may need to be aware of on servers where the Oracle “client” is shared by multiple users.
CD
You know how much fun it is when you have a master release script, either for deployment to an environment or, just as common these days, to refresh a Continuous Integration environment.
Such a script may well look something like this :
With SQLCL, the script becomes somewhat simpler :
Yes, you can now change directory from SQLCL, in the same way that you would at the OS level. As well as being rather convenient, this should serve to save a fair bit of typing, especially for scripts such as this one.
If all that isn’t quite enough Linux-like goodness for you then you’d probably be quite interested in…
ALIAS
As you may guess from the name, ALIAS allows you to save and invoke SQL or PL/SQL blocks under a pre=defined name.
There are already some aliases defined. You can find them by simply typing :
If you want to see the definition of the tables alias….
You can run this simply by typing the alias name at the prompt. For example, if you connect as the HR user …
Of course, you can add your own alias. If you’re an inveterate clock-watcher, for example….
The alias is now saved so, whenever I want to know if it’s nearly hometime….
According to the help, you can define aliases for PL/SQL blocks, and even reference bind variables.
Choosing you’re output format with SQLFORMAT
The new SQLFORMAT parameter deserves particular attention. Before I get into some of it’s more obvious benefits, take a look at this :
Now at first glance this script looks rather odd. Have I had an attack of fat-fingers ? Well, let’s run it and see…
Contrived example to show traffic-lighting
Yep, SQL*Plus now does colour. OK, so I set the threshold values to make sure I got records to show in all three colours, but you get the idea.
There’s a rather more comprehensive script by Kris Rice here, which details many colour options.
One of the neat features in SQLDeveloper has always been the ability to generate the output from a query in a pre-defined format.
This ability is still there in SDSQL. To use a fashionable example…
SQLFORMAT allows you to specify the output format separately from the code. For example :
The fact that the output format is separated from the script means that you can use the same script to generate multiple formats.
Ultimately, this means less code, not to mention less complex (or, at least, fiddly) code.
For example, instead of :
… you can now have…
…and if you need to create an additional feed file in a different format, you simply need to type…
…and execute exactly the same code again.
There are several SQLFORMAT options. The ones that I’ve tested are :
default
ansiconsole
csv
insert – lists resuts as an insert statement
loader – pipe-delimited
delimited – same as csv
xml
html
fixed – fixed width
text
json
INFORMATION = KNOWLEDGE = POWER = ENERGY = MATTER = MASS
This heading is a slight misquote of Terry Pratchett’s L-Space equation.
The theory of L-space is that a large collection of accumulated knowledge, such as a library, can act like a black hole.
If you want to have a go at bending the fabric of space and time armed with nothing more than a command line…
INFORMATION
The humble DESCRIBE command in SQL*Plus has been around for a while. I’d guess it’s probably as old as the tool itself.
In that time, things have changed a bit.
It’s now possible to have comments on columns. There’s also this new-fangled PL/SQL, together with it’s associated packages.
If, like me, you’ve hankered after a describe that’s just a bit more able to handle these more modern developments, well, you’re in luck.
First off, let’s issue a good old DESCRIBE :
Interesting message, let’s give it a go (note, it’s probably a good idea to set the SQLFORMAT to ansiconsole at this point) :
INFORMATION can give you even more in-depth information:
Whilst all of this additional functionality is very welcome, the best bit, for me at least, is this…
Yes, no longer will I have to scroll through the 86 members of DBMS_METADATA to find out the signature for GET_DDL.
I can now simply describe (OK, INFO) the package member directly. Just like going through a wormhole directly to the information I need (to stretch the L-space metaphor).
DDL
As the name suggests, DDL outputs the DDL for a given object. For example…
You also have the option of writing the output to a file. This command …
…writes the output to a file called regions_tab.sql, as well as the command line.
Whilst we’re on the subject of Time (well, saving some, at least)…
CTAS
CTAS – Create Table as Select – without all of that typing…
… generates DDL to create a new table called new_regions…
As CTAS does use DBMS_METADATA, you can amend the output using that package’s SET_TRANFORM_PARAM procedure. For example :
Remember, CTAS simply generates the DDL command to create the table and writes it to the buffer (and a file, if specified). It does not actually execute the command.
LOAD
The LOAD command offers an alternative way of populating a table from a CSV file.
Let’s try using a topical example…
The csv file is in a sub-directory called cricket :
At present, LOAD has a bit of a problem recognising that you’ve chnaged directory using the CD command. However, the developers have fixed this and it should be available in a future release.
In the meantime though, we can get this to work by specifying the relative path for the csv file :
NOTE – you need to issue a commit for these records to be saved permanently.
FORMAT
Being a rather particular breed, developers tend to like their code formatted in a certain way. The trouble is, if you ask ten developers what that way should be, you’re likely to get ten different answers.
It is for this reason that Coding Standards documents tend to illicit an inordinate amount of controversy.
As I’ve noted previously, one possible solution to this is to let developers format their code in whatever way they see fit and then just run the code through a formatting tool that converts it to whatever the standard is.
All the main IDEs have their own formatting solutions. However, you can now do this at the command line.
For example, let’s try this query :
Using the new FORMAT command, we can, now apply the default SQLDeveloper formatting…
Better still, we can even take the source file for the query, run it through the formatter and save the output to a new file :
The resulting file looks like this :
As you can probably tell by the output filename I’ve chosen, I’m not particularly keen on the default formatting.
I’ll admit, this is largely subjective on my part. However, I would like the code to be formatted according to my preferences rather than the default.
Once again, the developers have a solution for this which should be available in the near future.
With this functionality in place, you could potentially leave the contentious business of formatting code to the standard until just before deployment. Running each program through a formatter setup to meet your coding standards may be an appealing option.
BRIDGE
One of the major advantages IDE’s have over the command line is the ability to compare two schemas in different databases.
Up until now, this has not been possible from SQL*Plus without the use of a database link.
The BRIDGE command may well change all that.
At present, it’s functionality is restricted to creating a table in your current connection based on the results of a query run against a second connection that you specify. For example, to create a table in my current schema with the details of the tables in a remote hr schema….
…where the_hr_password is the password for the HR user on the target database.
One point to note here is that bridge executes the remote connection and the Create Table independently of each other.
Therefore, it’s quite possible for the connection to fail and the table to be created anyway.
NOTE – it should be possible to get around this behaviour by specifiying WHENEVER SQLERROR EXIT. I’ve not managed to get this to work as I’d expect, but this could be because I’m doing something silly.
Another thing to be aware of is that the BRIDGE command you’ve issued will be retained in the HISTORY, complete with uid/password.
This is currently on the developers’ to-do list.
APEX
The last of the new commands to cover is APEX.
If you simply issue this command without any arguments, it will list all of the APEX Applications where the user you are currently connected as is defined as the owning schema.
The exception to this is if you’re connected as SYS as SYSDBA where you’ll get a full list of all APEX applications.
So, connected as user OSSCA :
APEX also provides the facility to export an application. By spooling the output, you can create the appropriate file :
…creates the APEX export file my_apex_export.sql, as well as outputting to the screen.
Conclusion
Whilst it’s still very early days for SQLCL, it’s potential impact is significant. I make no apologies for being excited about it.
Where to go for more SDSQL stuff
There’s a fair amount of information out there about SDSQL.
A good starting point would be Jeff’s presentation, which is available here.
Both Kris Rice and Barry McGillin are members of the development team and post regularly on the subject of SDSQL.
If you find any glitches, or have any questions, then the place to go is the SQLDeveloper OTN Forum.
Finally, as it’s around Easter Time, try entering the following at the SQLCL command line :
Filed under: Oracle, SQL Tagged: alias, APEX, bridge, cd, colours in sqlcl, ctas, dbms_metadata.set_transform_param, ddl, format, history, information, load, multi-line editing in sqlcl, net, sdsql, sqlcl, sqlformat, tab completion in sqlcl 