The github repo for all the things the DB Tools team does will be posted here https://github.com/oracle/Oracle_DB_Tools 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: https://github.com/oracle/Oracle_DB_Tools/tree/master/sqlcl/lib

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
*
*/
helpers.exec=function(cmd){
    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.next().trim() : "";
        s = new Scanner(p.getErrorStream()).useDelimiter("\\A");
        ret.stderr = s.hasNext() ? s.next().trim() : "";

        p.waitFor();
        ret.rc = p.exitValue();
      p.destroy();
    return ret;
}


Putting it together with the BLOB loading example

load('https://raw.githubusercontent.com/oracle/Oracle_DB_Tools/master/sqlcl/lib/helpers.js');

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

/* 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 */
  binds.put("path",files[f]);
  binds.put("b",blob);

 /* 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;");
sqlcl.run();



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