Monday, February 01, 2016

Putting SQL in the corner with Javascript in SQLCL

Here's a pretty small javascript file that allows for running sql in the background of your current sqlcl session.  This is a trivial example of a sql script that has a sleep in it to simulate something taking time. It also prints the SID to show it's a different connection than the base.

select 'DB SID ' ||sys_context('USERENV','SID') || ' is going to sleep' bye from dual;

select 'DB SID ' ||sys_context('USERENV','SID') ||' is  awake' hi from dual;

Here's the script as of now and it's in GitHub here

// declare the 2 java files needed
var DriverManager = Java.type("java.sql.DriverManager");
var ScriptExecutor  = Java.type("oracle.dbtools.raptor.newscriptrunner.ScriptExecutor");

var BGsql="";
for(var i=1;i>args.length;i++){
  BGsql = BGsql + " " + args[i];

// Create a new connection to use for monitoring
// Grab the connect URL from the base connection in sqlcl
var jdbc = conn.getMetaData().getURL();
var user = 'klrice';
var pass = 'klrice';


// running the actual sql
function main(arg){
 function inner(){
    // make a new connect
  var conn2  = DriverManager.getConnection(jdbc,user,pass);  
  var sqlcl2 = new ScriptExecutor(conn2);  
  // run it;
 return inner;

// make a thread and start it up
// runs later
function runme(arg){
 // import and alias Java Thread and Runnable classes
 var Thread = Java.type("java.lang.Thread");
 var Runnable = Java.type("java.lang.Runnable");

 // declare our thread
 this.thread = new Thread(new Runnable(){
    run: main(arg)

 // start our thread

Thursday, January 07, 2016

Tuning SQL with Javascript in SQLCL

In case anyone missed it, #sqlcl has the ability to run javascript. This opens a lot of options.  Here's a simple example that shows how to using javascript.

  • Open a new Database Connection
  • Collect stats on the base connection
  • Do work on the main connection
  • Collect stats again
  • Only Print the stats that changed

In SQL Developer, the autotrace feature has for a long time selected the session statistics and display them.  Then you can diff those to another run and see what changes.  Jeff of course has blogs on this That's great but where's it leave a #sqlcl user? Enter JavaScript.

Here's the end result.  I ran a select count on all_objects filtered to DBA_%.  The things to notice is the Work Session's SID and Monitor Session's SID is different and printed to show I didn't cheat.  The command to execute is just passed as arguments to the script command.  The result is the count ran. I got the stats before and after.  Then it's just a simple loop comparing them to see what changed and print on the far right the actual difference.

The JavaSript is below and has comments enough to follow hopefully.  I'll post it to our #sqlcl github examples directory shortly.

// declare the 2 java files needed
var DriverManager = Java.type("java.sql.DriverManager");
var DBUtil  = Java.type("oracle.dbtools.db.DBUtil");

// Create a new connection to use for monitoring
var jdbc = conn.getMetaData().getURL();
var user = 'klrice';
var pass = 'klrice';
var conn2 = DriverManager.getConnection(jdbc,user,pass);
var util2 = DBUtil.getInstance(conn2);

// probably a better way to rpad a string
function pad(s,n){
   s = s.toString() + "                                                                    ";
   return ( s.substring(0,n));

// Simple function to grab the session stats and put them into JSON
function stats2json(sid){
     var binds = {};
     binds.SID = sid;

     var list = util2.executeReturnList("select name,value from v$statname sn,v$sesstat  s where sn.statistic# = s.STATISTIC# and s.SID= :SID",binds);
     var ret={};
     for (var i = 0; i < list.length; i++) {
             ret[list[i].NAME] = parseInt(list[i].VALUE);
   return ret;

// Simple function to grab the session details and put them into JSON
function getSession(util){
      /* Current active SQL Connection */
      var results = util.executeReturnList("select sys_context('USERENV','session_user') u,sys_context('USERENV','SESSIONID') sessionID,sys_context('USERENV','SID') sid from dual",null);

      var ret = {};
          ret.user      = results[0].U;
          ret.sid       = results[0].SID;
          ret.sessionid = results[0].SESSIONID;

      ctx.write("\tUser:" + ret.user + "\n");
      ctx.write("\tSID:" + ret.sid + "\n");
      ctx.write("\tSession:" + ret.sessionid + "\n");

     return ret;

// rebuild the arguments which are the commands to run
var sql="";
for(var i=1;i<args.length;i++){
  sql = sql + " " + args[i];

// print the sessions to prove they are different
ctx.write("--Work Session--\n");
var session = getSession(util);

ctx.write("--Monitor Session--\n");
var session2 = getSession(util2);

// grabt the stats before
var before = stats2json(session.sid);

// rebuild the sql from the arguments
ctx.write("Command:\n\t"+ sql + "\n\n");

// run it;

// get the after session stats
var after = stats2json(session.sid);

ctx.write("Session Stat Changes:\n\n");

// walk the stats and print the deltas
for(var key in before){
   // print only stats that changed
   if ( before[key] !=  after[key] ) {
     ctx.write( pad(key,64) + "\t\t "+ pad(before[key],12) + " --- "+ pad(after[key],12) + "\t "  );
     ctx.write( pad((after[key]-before[key]),12) + "\n");

Tuesday, December 15, 2015

Carbonated SQLCL - aka SODA support

     Oracle Database introduced JSON support.  This is supported in REST Data Services already and now in the latest SQLCL.  This means ORDS can serve up the access to client applications and developers can have a command line way to interact and test features/queries/... in a nut shell a shell for JSON document storage. To use this feature the database will be to have patch #20885778 applied.

KLRICE@orcl >soda


SODA allows schemaless application development using the JSON data model.
  SODA create  
       Create a new collection 

  SODA list 
       List all the collections 

  SODA get <collection_name> [-all | -f | -k | -klist] [{<key> | <k1> <k2> ... > | <qbe>}]
       List documents the collection 
       Optional arguments: 
      -all    list the keys of all docs in the collection 
      -k      list docs matching the specific  
      -klist  list docs matching the list of keys 
      -f      list docs matching the  

  SODA insert <collection_name> <json_str filename=""> 
       Insert a new document within a collection 

  SODA drop <collection_name> 
       Delete existing collection 

  SODA count <collection_name> [<qbe>] 
       Count # of docs inside collection. 
       Optional <qbe> returns # of matching docs

  SODA replace <collection_name> <oldkey> <new_ doc="" str=""> 
       Replace one doc for another 

  SODA remove <collection_name> [-k | -klist |-f] {<key> | <k1> <k2> ... | <qbe>} 
       Remove doc(s) from collection 
       Optional arguments: 
      -k     remove doc in collection matching the specific <key> 
      -klist remove doc in collection matching the list <key1> <key2> ... > 
      -f     remove doc in collection matching <qbe> 

KLRICE@orcl >

There's a lot in there for command so give them a try.  SODA is underpinned but tables with JSON data in them.  It's very easy to get going testing out the SODA features.  Here's a simple collection create, insert, and query.

KLRICE@orcl >soda create ODTUG
Successfully created collection: ODTUG

KLRICE@orcl >soda insert ODTUG {"content":"awesome"}
Json String inserted successfully.

KLRICE@orcl >soda insert ODTUG {"location":"chicago"}
Json String inserted successfully.

KLRICE@orcl >soda insert ODTUG {"best_of_week":"DB Symposium"}
Json String inserted successfully.

KLRICE@orcl >soda get ODTUG -f {"content":{"$startsWith":"a"}}

Key:      7A01B31EBD7C484C88F431B8871DD165
Content:  {"content":"awesome"}
 1 row selected. 

JavaScript and SODA

Here's a quick example of using the SODA libraries from javascript.  Also in here is a new feature of scripting.  The javascript can be inlined into a sql script.  Notice that it's soda.sql <<< .sql that has javascript in it, let that sink in for a minute with the possibilities.

KLRICE@orcl >@soda.sql

Caffeinated SODA 
Dropping the old
Created the old
Inserted Document
{ "name" : "Alex", "friends" : "50" }

KLRICE@orcl >!cat soda.sql

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

var OracleRDBMSClient = Java.type("oracle.soda.rdbms.OracleRDBMSClient");
var OracleDataSource = Java.type('oracle.jdbc.pool.OracleDataSource');
var cl = new OracleRDBMSClient();
var db = cl.getDatabase(conn);

ctx.write("Dropping the old\n")
var foo = db.admin().createCollection("foo");
ctx.write("Created the old\n")
    foo = db.admin().createCollection("foo"); 

var doc = db.createDocumentFromString("{ \"name\" : \"Alex\", \"friends\" : \"50\" }");
ctx.write("Inserted Document\n")


var f = db.createDocumentFromString(JSON.stringify( {"name" : { "$startsWith" : "A" }}));
var c = foo.find().filter(f).getCursor();

while (c.hasNext()) {
 cDoc =;
 ctx.write(cDoc.getKey() + "\n");



Gory Details of the release

Someone asked about change logs for these SQLCL updates.  Here's a filtered version of the gory details

Thursday, October 15, 2015

SQLCL - Blob loading ALL the files

The github repo for all the things the DB Tools team does will be posted here This will include sqlcl , sqldev, modeler, ords , and who knows what other things we have up our sleeves to post.  This repo is going to be made up of  examples and getting started things like a new extention for sqldev, custom transformation for modeler, ords integration with handsontable, or sqlcl scripting.

Now onto more BLOB loading.

I'm slowly building out a helper.js which is now posted and I'll continue to update it on github here:

There's a new function in there to exec host commands and capture the stdout, stderr, and return code of the command.  Now coupling that with the BLOB loading and you can load an entire directory of files into a table with a small amount of javascript.

New helper function

*  Runs the passed in command and returns an Object with
*   .rc      - the return code
*   .stdout  - STDOUT
*   .stderr  - STDERR
    var RunTime  = Java.type("java.lang.Runtime");
    var Scanner  = Java.type("java.util.Scanner");
    var p = RunTime.getRuntime().exec(cmd);

    var ret={};
        s = new Scanner(p.getInputStream()).useDelimiter("\\A");
        ret.stdout = s.hasNext() ? : "";
        s = new Scanner(p.getErrorStream()).useDelimiter("\\A");
        ret.stderr = s.hasNext() ? : "";

        ret.rc = p.exitValue();
    return ret;

Putting it together with the BLOB loading example


/* Clean out the table just to prove it's not a baked demo */
sqlcl.setStmt("truncate table k");;

/* File name */
var files= helpers.exec('find . -maxdepth 1 -type f ').stdout.split('\n');

/* bind map for reuse */
var binds = helpers.getBindMap();

for(f in files ) {
   //  ctx.write("Loading : " + files[f] + "\n");
  /* load the blob */
  blob = helpers.getBlobFromFile(files[f]);

  /* assign the binds */

 /* Just do it */
  var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);

/* print the results to see if it worked */
sqlcl.setStmt("select path,dbms_lob.getlength(blob_content) from k order by when desc;");;

Removing the truncate and select at the end, loading the entire directory of client side files is 7 lines of javascript.

Wednesday, October 14, 2015

AskTom's print_table - SQLCL version

There was an idea logged over on to add AskTom style print_table to sqlcl.

Here's how easy it is now that there's scripting in sqlcl.  This is a simple js file that loops over the rows and columns and prints them.  When using the executeReturnListofList the first row is the name of the columns for reference.  With that data there it's quite simple to customize any output.

/* rebuild the sql passed in since args is split on spaces */

var sql="";
for(var i=1;i<args.length;i++){
  sql = sql + " " + args[i];
ctx.write(sql + "\n\n");

var ret = util.executeReturnListofList(sql,null);

/* loop the rows */
for (var i = 0; i < ret.size(); i++) {
    ctx.write('>ROW \n'); 
    /*loop the cols */
    for( var ii=0;ii<ret[i].size();ii++) {
        ctx.write("\t" + ret[0][ii] + " : " + ret[i][ii] + "\n");


Now running it is very simple.  Script printtab select......

Disqus for Kris' Blog