Extending Oracle SQL Developer Command Line (SQLcl) with JavaScript: Single Record Viewer

Oracle SQL Developer Command Line (SQLcl) is the full name for what we’ve been calling, SQLcl.

Have you seen the video? It’s only 8 minutes and will catch you up on just what SQLcl is.

Anyways, there’s a ‘new’ command called SCRIPT that I want to show you today. I say ‘new’ because we added this in October of 2015.

Stealing from @krisrice…

This new command can radically change things you can do in the tool. The script command runs a script that can be any language that is JSR-223 enabled. The best list I can find is here: https://en.wikipedia.org/wiki/List_of_JVM_languages So you want to program sql scripting in groovy,perl,javascript,pascal,…. All doable now.

To run a script simple type: script script_name If there is no file extension passed along it assume a .js The language is loaded based on this extension. If the language can’t be loaded you get a message saying as much “Could not load Scripting Engine for :py” More on how to add the other languages later.

Good News: You Don’t Even Have to Write the JavaScript, Mostly

We’ve been publishing examples on GitHub, and there are 23 so far. While they may not do exactly what you want, they do expose all the underlying principals and concepts needed to write your own.

Here’s a fun one.

This SCRIPT will take the results of a query, and print them like on an index card, or kind of like you see in SQL Developer’s Single Record Viewer.

So how do you use it?

Grab the code, save it to a file.

I saved this to ‘cardview.js’ in my SQLcl/bin folder.

You call the the javascript by using the SCRIPT command. You pass the name of the file over along with any arguments.

In this case, you pass it the query you want the results formatted to an index card type view.

/*  rebuild the sql that was passed in as args*/
var sql="";
for(var i=1;i<args.length;i++){
  sql = sql + " " + args[i];
}

You can see where Kris is taking in the argument passed to the script and is building out the SQL string.

I’m not going to talk more about the code, because it’s pretty easy to follow, and I would only make it sound harder than it is. I did change it up a little bit for this post. I added row #’s to the output.

I haven’t been blogging much because we’ve been working hard to get stuff ready for Oracle Open World next week. I’ll be doing a What’s New and Tips & Tricks talk where I’ll be showing stuff like this.

Want another fun example? I took the AUDIO.JS example on GitHub and used it to add a startup sound to SQLcl.

SQLcl Startup, A-10 edition https://t.co/8SbWKZxLSZ HT @krisrice

— SQLcl (@oraclesqlcl) September 7, 2016