As mentioned once or twice or 100 times, sqlcl exposes javascript scripting with nashorn to make things very scriptable. To learn more on Nashorn itself there's a lot of great write ups such as http://www.oracle.com/technetwork/articles/java/jf14-nashorn-2126515.html So far, the scripting examples have been along the lines of conditional or looping of existing sqlcl commands.
Here's an example of creating a brand new command only from javascript. This is a pretty simple one that for ALL command will snapshot the start time and print the elapsed time. It also adds the new command "kris".
Just to show this is really nothing that new to sqlcl,
here's a blog post from 2006 about how to make a Java based CommandListener in SQL Developer. This hasn't changed since then.
This all adds up to if we forget to add some feature, you want to override a command, perform something before or after commands, it's very simple to DIY your sqlcl.
// SQLCL's Command Registry
var CommandRegistry = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandRegistry");
// CommandListener for creating any new command
var CommandListener = Java.type("oracle.dbtools.raptor.newscriptrunner.CommandListener")
// Broke the .js out from the Java.extend to be easier to read
var cmd = {};
// Called to attempt to handle any command
cmd.handle = function (conn,ctx,cmd) {
// Check that the command is what we want to handle
if ( cmd.getSql().indexOf("kris") == 0 ){
ctx.write("Hi Kris, what up?\n");
// return TRUE to indicate the command was handled
return true;
}
// return FALSE to indicate the command was not handled
// and other commandListeners will be asked to handle it
return false;
}
// fired before ANY command
cmd.begin = function (conn,ctx,cmd) {
var start = new Date();
// stash something for later like the start date
ctx.putProperty("cmd.start",start);
}
// fired after ANY Command
cmd.end = function (conn,ctx,cmd) {
var end = new Date().getTime();
var start = ctx.getProperty("cmd.start");
if ( start ) {
start = start.getTime();
// print out elapsed time of all commands
ctx.write("Elapsed Time:" + (end - start) + "\n");
}
}
// Actual Extend of the Java CommandListener
var MyCmd2 = Java.extend(CommandListener, {
handleEvent: cmd.handle ,
beginEvent: cmd.begin ,
endEvent: cmd.end
});
// Registering the new Command
CommandRegistry.addForAllStmtsListener(MyCmd2.class);
The interesting part is on the Parameters tab. There is where to define the IN , OUT, or IN/OUT. These are a INs can be from the URI or from the HTTP Header. This means if you want USER_AGENT to be useful in the the plsql block just define it and assign it to a bind variable. Here is the :ct and :myval being defined. The :ct is bound to the HTTP HEADER Content-Type. The :myval is bound to RESPONSE. More robust support for UDTs is in the works.
