Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Using SQLcl to write out BLOBs to files…in 20 lines of js

2 Mins Read

In fairness, it’s really only 8 lines of code, but @krisrice is a fan of comments.

Here’s the setup:

I have a query I want to run, where I can dump out a BLOB column in my table to separate files. And I need to automate this.

A customer, in not so many words, more or less

Using the SCRIPT command in SQLcl

SQLcl will let you run whatever SQL or PL/SQL you want. And it has quite a few commands that help you, like DDL and INFO.

But, it ALSO has a mechanism where you can call out to the Nashorn engine in Java to execute things written in JavaScript (or jython or about 20 other dialects).

Kris has quite a few examples up on his blog and gisthub, but I couldn’t find one that did this particular task. I asked him, he pasted it to me, and now I share it with YOU.

Grab this text, and save it as a .SQL file

script
// issue the SQL
 
var binds = {}
var  ret = util.executeReturnList('select id,file_name,content from media',binds);
 
// loop the results
FOR (i = 0; i < ret.length; i++) {
  // debug IS nice
  ctx.write( ret[i].ID  + "\t" + ret[i].FILE_NAME+ "\n");
 
  // GET the BLOB stream
  var blobStream =  ret[i].CONTENT.getBinaryStream(1);
 
  // GET the path/file handle TO WRITE TO
  var path = java.nio.file.FileSystems.getDefault().getPath(ret[i].FILE_NAME);
 
  // dump the file stream TO the file
  java.nio.file.Files.copy(blobStream,path);
 
}
/
!dir

Now, this SQL script won’t run in SQL*Plus. And if you look at it, it’s really js…but the first line is the SCRIPT command in SQLcl, what follows is a chunk of code, and at the end it says ‘run this script’ and on WINDOWS, give me a directory listing.

The Query is hard-coded on line #5. It assumes you have a filename and a BLOB that you can access.

The file name the blob column names can be changed in the query, but then you’ll ALSO need to change the CONTENT and FILE_NAME attributes for the ret[i] array mentioned on lines 10, 13, & 16.

Once you have your query settled, you’re ready to run the script. Simply @script.sql in SQLcl.

It will print to screen a listing of the files as it’s writing out the same files to the current working directory. At the end, it issues the DIR command to show you the actual files.

Ta-da!

And if I browse to that directory, I can see my files came out, A-OK!

You can almost have SQLcl do ANYTHING

Load a directory of files as BLOBs, check.

Building an Excel worksheet, check.

…

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

9 Comments

  1. Brian Reply

    Is this the best way to dump out all BLOBs from a database into plain text? I’ve searched Google, AskTom, MOS, and cannot find a simple way to export BLOB columns to ASCII/raw text that are ~300KB in size each.

    Any advice is greatly appreciated!

    • thatjeffsmith Post Author

      Storing 300KB files as BLOBs in the database to begin with is a debatable design choice…external storage via BFILES or EXTERNAL TABLES might do better, but I don’t know your use case.

      I don’t know of an ‘easy’ route for you. You might just want to write some Java code to do it. But how many records/files are we talking about?

  2. Brian Powell Reply

    Changing FOR to lowercase worked for me too. It eliminated the syntax error. Could it be a operating-system-specific issue? Whether SQLcl’s Java VM is on a case-sensitive system (Linux, MacOS) or a case-insensitive system (Windows) ?

  3. Ketan Kothari Reply

    I think the error is FOR needs to be in lower case for. That fixes the issue. I also have a new requirement. The blob I am retrieving is compressed using zlib and I have a sample java code to uncompress it. Hoever I am not able to figure out how to convert to scripting syntax.

    I need to invoke inflater class and some how pass the blobstream as Bytes

    My Java Code is

    ResultSet rs =
    stmt.executeQuery(“SELECT IBTRANSACTIONID, IB_SEGMENTINDEX, SEGMENTNO, SUBSEGMENTNO, UNCOMPMIMEDATALEN, MIMEDATALONG\n” +
    ” FROM SYSADM.PSAPMSGPUBDATA WHERE IBTRANSACTIONID = (Select IBPUBTRANSACTID FROM SYSADM.PSAPMSGSUBCON\n” +
    ” WHERE IBTRANSACTIONID = ‘” + pub_trans_id + “‘)\n” +
    ” ORDER BY IBTRANSACTIONID, IB_SEGMENTINDEX, SEGMENTNO, SUBSEGMENTNO, DATASEQNO\n”);
    String id = “”;
    while (rs.next()) {
    // if(!id.equals(rs.getString(“IBTRANSACTIONID”))) {
    id = rs.getString(“IBTRANSACTIONID”);
    String segment = rs.getInt(“IB_SEGMENTINDEX”) + “-” +
    rs.getInt(“SEGMENTNO”) + “-” +
    rs.getInt(“SUBSEGMENTNO”);
    Inflater inflater = new Inflater();
    byte[] result = new byte[rs.getInt(“UNCOMPMIMEDATALEN”)];
    inflater.setInput(rs.getBytes(“MIMEDATALONG”));
    int length = inflater.inflate(result);
    String result1 = new String(result, 0, length, “UTF-8”);
    result1 = result1.replace(“UTF-16″,”UTF-8”);

  4. Brian Powell Reply

    Not sure what I’m doing wrong Jeff. I’m getting this error in the script:

    javax.script.ScriptException: :7:10 Expected , but found ;
    FOR (i = 0; i < ret.length; i++) {
    ^ in at line number 7 at column number 10
    at jdk.nashorn.api.scripting.NashornScriptEngine.throwAsScriptException(NashornScriptEngine.java:470)

    The only change I made was the query and the column names in the “ret” list.
    This was with a well used install of SQLcl 20.2.0.174.1557 on MacOS.
    Thanks!

    • Ketan Kothari

      Please fix your original script and change FOR to for. Otherwise it shows syntax error. I need to know the syntax of how to change the variable blobStream to bytes so that I can pass it to inflater class inflate method which accepts the input in bytes. Not sure if Kris rice can help.

    • thatjeffsmith Post Author

      i pasted in the exact code i used for the scenario, FOR is working for me…Kris can help, ping him at @krisrice on twitter

Write A Comment