SQLCL - Blob loading ALL the files

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.

AskTom's print_table - SQLCL version

There was an idea logged over on community.oracle.com 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");
    } 
}

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

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

SQLCL - Blob loading from a file

There is a ton of ways having new scripting in sqlcl can help.  One example is a simpler way to load a local file into a BLOB in a column.

Prior to this the only options I know of would be to write custom code or move the files to the server and use utl_file or xdb or some combination of things like this.

Enter SQLCL and javascript method #1.

Here's a simple script which loads a local file into a temp BLOB and uses that as a bind variable in the insert statement

/*
/*
*  Function to take in a filename and add or create it to a map
*  with bind variables
*/
function addBindToMap(map,bindName,fileName){
     /*  conn is the actual JDBC connection */
     var b = conn.createBlob();

     var out = b.setBinaryStream(1);

     var path = java.nio.file.FileSystems.getDefault().getPath(fileName);

     /* slurp the file over to the blob */
     java.nio.file.Files.copy(path, out);
     out.flush();

     if ( map == null ) {
         /* java objects as binds needs a hashmap */
         var HashMap = Java.type("java.util.HashMap");
         map = new HashMap();
     }
     /* put the bind into the map */
     map.put("b",b);
 return map;
}


/* File name */
var file = "/Users/klrice/workspace/raptor_common/10_5.log";

/* load binds */
binds = addBindToMap(null,"b",file);

/* add more binds */
binds.put("path",file);

/* exec the insert and pass binds */
var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);

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

sqlcl.run();

Running it is as expected.  One new row and showing the blob size.  The only difference here is that you can avoid typing s-c-r-i-p-t and save 2 letters with just scri.

Method #2 shared library

I'll be making a library intended to make some things easier such as file loading and creating the hashmap.  So taking out this blob loading as an example there first 2 things to put into such a library in helpers.js

/*
    This is a javascript library to make some of the common things
    in SQLCL simplier.
*/

var helpers = {} ;

/* for complex type a java hashmap is needed for binds */
helpers.getBindMap = function(){
   var HashMap = Java.type("java.util.HashMap");
   map = new HashMap();
   return map;
};

/* create a temp blob and load it from a local to sqlcl file */
helpers.getBlobFromFile=function (fileName){
     var b = conn.createBlob();
     var out = b.setBinaryStream(1);
     var path = java.nio.file.FileSystems.getDefault().getPath(fileName);
     java.nio.file.Files.copy(path, out);
     out.flush();
 return b;
};

Now the blob loading script can be reduced to something like this

load('helpers.js');

/* File name */
var file = "/Users/klrice/workspace/raptor_common/10_5.log";

/* load binds */
binds = helpers.getBindMap();

/* add more binds */
binds.put("path",file);

blob = helpers.getBlobFromFile(file);

binds.put("b",blob);


/* exec the insert and pass binds */
var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);

I'll be cleaning up this helpers.js and hosting it soon with frequent additions to new functions.

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");
}

SQLcl - Oct 5th Edition

It was time for another push of the new sql command line , sqlcl.  Here's the changes in the latest build.  There are a number of bugs fixed relating to connecting to idle databases, alias feedback, and picking up the $ORACLE_HOME's jdbc driver when available.  There is only a couple functional changes.


First is that now the jdbc connect string itself can be specified.  This opens up a number of options on how to connect. This is list of valid jdbc prefixes.  While I've not yet tested it as noted in this list we will be testing loading sqlcl into the DB and trying it out in the JVM there.

• jdbc:oracle:thin
• jdbc:oracle:oci8
• jdbc:oracle:kprb
• jdbc:default:connection
• jdbc:oracle:kprb:
• jdbc:default:connection:

Next is the ansiconsole formatting of numbers was changed up a little.  The new syntax as 3 options.  One piece of feedback was that the numbers auto formatting with separators made some things difficult.  Now just setting the console formatter will NOT format numbers.  Instead the default option needs to be specified.  Also is the option to specify any number format which has to be in the form of a java format.  The syntax can be read here.

• set sqlformat ansiconsole
• set sqlformat ansiconsole default
• set sqlformat ansiconsole <number format>

This script show an example of all the new options.

The only caution when using the @|... |@ syntax is that only one thing can be used at a time as the, used to separate the formatting options gets read by the java formatter as a number format.  If this is something that folks end up using a lot, I'll come up with something to let it blink and be red at the same time!