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.
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.
9 Comments
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!
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?
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) ?
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”);
You need a js version of this…
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!
please share your entire script, you trying to use it, and the entire error stack
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.
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