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.