Search notes:

SQLcl: put a BLOB into an Oracle table and get it again

This example tries to demonstrate how BLOBs can be written and read into/from an oracle table with SQLcl scripting.
The example was inspired by Kris Rice's example on SQLCL - Blob loading ALL the files.
In order to understand the mechanisms behind the code a bit better, I slightly rewrote the code.

create-table.js

First, we need a table into which the BLOB is going to be written. If the table already exists, it will be deleted so as to start cleanly. 
var cnt = util.executeReturnOneCol('select count(*) from user_tables where table_name = :t', {t: 'LOB_EXAMPLE'});

function execStmt(stmt) {
   sqlcl.setStmt(stmt);
   sqlcl.run();
}

if (cnt > 0) {
   print('Table lob_example exists, dropping it.');
   execStmt('drop table lob_example');
}

execStmt(
  'create table lob_example('            +
  '  filename varchar2(50) primary key,' +
  '  data     blob'                      +
  ')'
);
Github repository about-SQLcl, path: /script/lob/create-table.js
With SQLcl, the script is started like so: 
rene@ORA19> script create-jab.js

put-image.js

put-image.js loads the content of an file with an image (png) from the local file system and inserts it as BLOB into a new record in the table we have created: 
function putImage(filename) {

   var blob   = conn.createBlob();
   var stream = blob.setBinaryStream(0);
   var path   = java.nio.file.FileSystems.getDefault().getPath(filename);
   java.nio.file.Files.copy(path, stream);
   stream.flush();

   var ret=util.execute(
      'insert into lob_example(filename, data) values (:filename , :data)',
      { filename: filename,
        data    :  blob }
   );

   if (!ret) {
      print('Something unintended happened.');
   }

}

putImage('1000x650.png');
conn.commit();
Github repository about-SQLcl, path: /script/lob/put-image.js

get-image.js

With the image now in the database, we can get it again and save it locally: 
var images = util.executeReturnList('select filename, data from lob_example',{});

images.forEach(function(image) {

   print( image.FILENAME);

   var blobStream =  image.DATA.getBinaryStream(0);
   var path = java.nio.file.FileSystems.getDefault().getPath(image.FILENAME);

   java.nio.file.Files.copy(blobStream, path);

});
Github repository about-SQLcl, path: /script/lob/get-image.js

Index