SQLcl - Oct 13th Edition

Another build of SQLCL is out.  This one has lots of great things in it.  There's fix in there to work with Cygwin for the unlucky people that are still on windows.

Now the new stuff:  SCRIPT

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.

Javascript is the only one built into Java by default so most example will be focused on that although I do have some Jython ones also to post.

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.

Here's a simple script:

ctx.write("Hold my beer, I'm going to try something\n\n");

ctx.write('hi from javascript\n');

ctx.write('This is a count of my objects');
sqlcl.setStmt('select count(1) from user_objects; ');
sqlcl.run();

var ps1= 'set sqlprompt "@|blue  _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue 🍼 >|@"'

ctx.write('Now javascript will change the sqlprompt\n');
sqlcl.setStmt(ps1);
sqlcl.run();

sqlcl.setStmt('@numbers.sql');

sqlcl.run();

This example while simple show you can run commands from inside the script that affects sqlcl itself like changing the prompt.  The simple example is that I connected to production so a baby bottle it swapped out for beer

There's a few globals available for use.  There'll be more formal doc to follow

    sqlcl

        Methods:  

             setStmt(<String of stuff to run>)

                 This can be a single statement, an entire script of stuff, or any sqlcl command such as "@numbers.sql"

             run()

                 Runs whatever is in the stmts

   ctx ( this has tons of methods but this is the single most important )

       Methods:

             write(<String>)

   util ( again tons of methods ) 

       Methods:

              execute(<string>,binds)

                   executes whatever is passed in with a boolean return for success/failure

              executeReturnOneCol(<string>,binds)

                   executes and returns the first row , first column

              executeReturnListofList(<string>,binds)

                   executes and returns an array(rows) of arrays(row).  

              executeReturnList(<string>,binds)

                   execute and returns and array ( rows ) of objects ( row )

              

Here is an example using some of these methods with the expectable output shown below.

/* Look up a single value to use in a bind later */
var user = util.executeReturnOneCol('select user from dual');

var binds = {};
binds.name = 'EMP';
binds.who = user;

ctx.write('Using Binds:'+ binds + '\n');

ctx.write('***************************************************************\n');
ctx.write('*****    SIMPLE LOOP OF LIST OF LIST                ***********\n');
ctx.write('***************************************************************\n');


var ret = util.executeReturnListofList('select object_name,object_type from  all_objects where object_name = :name and owner = :who ',binds);

for (var i = 0; i < ret.length; i++) {
    ctx.write( ret[i][1]  + "\t" + ret[i][0] + "\n");
}

ctx.write('\n\n');

ctx.write('***************************************************************\n');
ctx.write('*****    SIMPLE LOOP OF LIST OF NAMES WITH BINDS     **********\n');
ctx.write('***************************************************************\n');


ret = util.executeReturnList('select object_name,object_type from  all_objects where object_name = :name and owner = :who ',binds);

for (i = 0; i < ret.length; i++) {
    ctx.write( ret[i].OBJECT_TYPE  + "\t" + ret[i].OBJECT_NAME+ "\n");
}