CODEWITHSUNDEEP
oracle-databaseblob
Anbu
Anbu

Reputation: 953

how to export and import BLOB data type in oracle

how to export and import BLOB data type in oracle using any tool. i want to give that as release

Upvotes: 4

Views: 38462

Answers (1)

Kris Rice
Kris Rice

Reputation: 3410

Answering since it has a decent view count even with it being 5 year old question..

Since this question was asked 5 years ago there's a new tool named SQLcl ( http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html)

We factored out the scripting engine out of SQLDEV into cmd line. SQLDev and this are based on java which allows usage of nashorn/javascript engine for client scripting. Here's a short example that is a select of 3 columns. ID just the table PK , name the name of the file to create, and content the BLOB to extract from the db.

The script command triggers this scripting. I placed this code below into a file named blob2file.sql

All this adds up to zero plsql, zero directories instead just some sql scripts with javascript mixed in.

script
// issue the sql
// bind if needed but not in this case
var binds = {}
var  ret = util.executeReturnList('select id,name,content from images',binds);

// loop the results
for (i = 0; i < ret.length; i++) {
   // debug messages
    ctx.write( ret[i].ID  + "\t" + ret[i].NAME+ "\n");

   // get the blob stream    
     var blobStream =  ret[i].CONTENT.getBinaryStream(1);

   // get the path/file handle to write to
   // replace as need to write file to another location
     var path = java.nio.file.FileSystems.getDefault().getPath(ret[i].NAME);

   // dump the file stream to the file
     java.nio.file.Files.copy(blobStream,path);
}
/

The result is my table emptied into files ( I only had 1 row ). Just run as any plain sql script.

SQL>  @blob2file.sql
1   eclipse.png
blob2file.sql   eclipse.png

SQL>

Upvotes: 1

Related Questions