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.