8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Export BLOB

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 the Top.