Related articles.
The following article presents a simple method for exporting the contents of a BLOB datatype to the filesystem.
First we create a Java stored procedure that accepts a file name and a BLOB as parameters.
CREATE OR REPLACE JAVA SOURCE NAMED "BlobHandler" AS import java.lang.*; import java.sql.*; import oracle.sql.*; import java.io.*; public class BlobHandler { public static void ExportBlob(String myFile, BLOB myBlob) throws Exception { // Bind the image object to the database object // Open streams for the output file and the blob File binaryFile = new File(myFile); FileOutputStream outStream = new FileOutputStream(binaryFile); InputStream inStream = myBlob.getBinaryStream(); // Get the optimum buffer size and use this to create the read/write buffer int size = myBlob.getBufferSize(); byte[] buffer = new byte[size]; int length = -1; // Transfer the data while ((length = inStream.read(buffer)) != -1) { outStream.write(buffer, 0, length); outStream.flush(); } // Close everything down inStream.close(); outStream.close(); } }; / ALTER java source "BlobHandler" compile; show errors java source "BlobHandler"
Next we publish the Java call specification so we can access it via PL/SQL.
CREATE OR REPLACE PROCEDURE ExportBlob (p_file IN VARCHAR2, p_blob IN BLOB) AS LANGUAGE JAVA NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)'; /
Next we grant the Oracle JVM the relevant filesystem permissions.
DECLARE l_schema VARCHAR2(30) := 'SCHEMA_NAME'; BEGIN DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete'); DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', ''); DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', ''); END; /
Finally we can test it.
CREATE TABLE tab1 (col1 BLOB); INSERT INTO tab1 VALUES(empty_blob()); COMMIT; DECLARE l_blob BLOB; BEGIN SELECT col1 INTO l_blob FROM tab1; ExportBlob('c:\MyBlob',l_blob); END; /
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/8i/export-blob