Saving Private BLOB: Extracting a BLOB to the Local Filesystem With SQLcl.

Using a hybrid SQLcl script to fetch a BLOB from a procedure and writing it to the local (client) filesystem.

Let’s assume you have a stored PL/SQL procedure that returns a BLOB in an out(put) parameter

create or replace procedure get_blob(p_id in number, p_content out blob)
is
begin
  -- code to fetch BLOB into p_content
  p_content := -- some code
end;
/

To call this procedure in PL/SQL you would use:

declare
  l_content blob;
begin
  get_blob(1, l_content);
end;
/

Now the challenge is to save l_content to a file.

SQLcl allows you to write script with JavaScript, which can allow you to write to the local file system,like UTL_FILE, which only writes to the file system of the database server.

Our script with combine PL/SQL and JavaScript to get this task done.

variable bv_file_path_and_name varchar2(1000) 
variable bv_id number
variable bv_file_contents blob

begin

  :bv_file_path_and_name := '&1';
  :bv_id     := '&2';

  -- fetch BLOB into bind variable
  get_blob(
    p_application_id => :bv_id,
    p_content        => :bv_file_contents
  );
end ;
/  

script
var c_copy_option                 = Java.type( "java.nio.file.StandardCopyOption" );
var v_sqlcl_binds                 = ctx.getVarMap();
var v_file_path_and_name          = v_sqlcl_binds.get( "BV_FILE_PATH_AND_NAME" ).getValue();
var v_file_path_obj               = java.nio.file.Paths.get( v_file_path_and_name );
var v_file_contents               = v_sqlcl_binds.get( "BV_FILE_CONTENTS" );   
var v_file_contents_obj           = v_file_contents.getValueObj() ;
var v_file_contents_binary_stream = v_file_contents_obj.getBinaryStream(1) ;

java.nio.file.Files.copy( 
  v_file_contents_binary_stream, 
  v_file_path_obj, 
  c_copy_option.REPLACE_EXISTING
);
/

The first three line declare the necessary bind variables.

The anonymous PL/SQL block sets the bind variable values and fetches the BLOB into :bv_file_contents.

Lines 18ff are the JavaScript portion of the script. It sets a number of variables, including the filename (line 22) and the file contents (line 25).

Finally a file is written to the local file system (line 29).

Example: Function Returning BLOB

Given that we have a function that returns a BLOB, we can shorten the syntax to pure JavaScript:

var c_copy_option = Java.type( "java.nio.file.StandardCopyOption" );
var binds    = {}
binds.id     = args[1]
var filename = args[2];

var apps = util.executeReturnList('select get_blob(:id) myblob from dual',binds);

apps.forEach( function(app) {
    java.nio.file.Files.copy(
        app.MYBLOB.getBinaryStream(0), 
        java.nio.file.Paths.get(filename), 
        c_copy_option.REPLACE_EXISTING);
})

Call this with positional arguments:

SQL> script example.js 1 foo.png

I hope this helps.

Jeff Smith also has a solution that queries the BLOB columns in a table and writes them to individual files.

A big Thank You goes out to my Oracle colleague Joseph Fuda for providing me with the solution and allowing me to present it in this post. Check out his useful snippets at http://www.sqlsnippets.com/en/home.html.