How to pull out blob content out to a directory using kettle

This post related to , if you had saved the blob data in your table then how to get your data out from a database table, its quiet easy. Some of the database also have their own algorithm of doing this task, but i am doing this with ETL kettle tool which is pretty fast(its not pretty its incredibly fast), proceed with follow steps:

1. create table, add some blob data
2. create transformation and execute it


1. Create table and add blob data
follow below step/post

CREATE TABLE t (id NUMBER(10),doc BLOB, name varchar2(30) );

CREATE SEQUENCE t_seq;

CREATE OR REPLACE DIRECTORY documents AS 'd:\app';

CREATE OR REPLACE PROCEDURE upload_t (file_name_v IN varchar) AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO t (id, doc,name) vALUES (t_seq.NEXTVAL, empty_blob(),file_name_v) RETURN doc INTO v_blob;
v_bfile := BFILENAME('DOCUMENTS', file_name_v);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);

COMMIT;
end;
/

EXEC upload_t('New Text Document.txt');


2. Build transformation as per below image:

Add below java script to step "Modified Java Script Value"

var File = java.io.File;
var FileOutputStream = java.io.FileOutputStream;
var BufferedOutputStream = java.io.BufferedOutputStream;
var out_msg;
var err_msg;
var filename = substr(getVariable('Internal.Transformation.Filename.Directory', '') +'/'+file_name, 7);
var outBytes = file;
try{
    var f = new File(filename);
      if (!f.exists()){
        f.createNewFile();
    }
 
    var fos = new FileOutputStream(f);
    var bos = new BufferedOutputStream(fos);
    bos.write(outBytes);
    bos.flush();
    bos.close();
 
    out_msg = 'succeed';
//    err_msg = 'OK';
 
}
catch (e){
    out_msg = 'Failed';
    err_msg = e.javaException.toString();
}

Save and execute transformation

It will fetch the blob data and build the file at path same as of transformation path. Moreover you can modify javascript to add custom path.

NOTE*- file_name must contain the value as file name with postfix extension (format - filename.extension).

BENIFIT - benefit of using this code is it will work for ORACLE and SQLServer Database (these two are tested by me)
Oracle - Blob datatype
SQLServer - Image and Varbinary(max) datatype