There is more than one post on how to unload blob from the database, mostly in plsql with utl_file.put_raw (see note 330146.1) and with java with FileOutputStream (see note 247546.1)
Unfortunately both are terribly slow due to the 32k limitation of put_raw in utl_file and due to a low “optimum buffer size” retrieved by myBlob.getBufferSize(), I increased the java stream buffer to 20M or to the size of the lob, whichever is smaller.
The code is mostly copy-pasted from metalink. But I changed the size from getbuffersize() to length().
Using java for large blob (read large binary large object) is about 3x faster than plsql in this test.
SQL> CREATE USER USER1 IDENTIFIED BY SeCrEt;
User created.
Elapsed: 00:00:00.04
SQL>
SQL> GRANT CREATE SESSION , CREATE PROCEDURE TO USER1;
Grant succeeded.
Elapsed: 00:00:00.01
SQL>
SQL> connect user1/SeCrEt
Connected.
SQL>
SQL> CREATE OR REPLACE JAVA SOURCE NAMED "BlobHandler"
2 AS import java.lang.*;
3 import java.sql.*;
4 import oracle.sql.*;
5 import java.io.*;
6 public class BlobHandler {
7 public static void ExportBlob(String myFile, BLOB myBlob) throws Exception {
8 File binaryFile = new File(myFile);
9 FileOutputStream outStream = new FileOutputStream(binaryFile);
10 InputStream inStream = myBlob.getBinaryStream();
11 int size;
12 if (myBlob.length()> 20000000) { // tune this to whatever appropriate value
13 size = 20000000;
14 } else {
15 size = (int)myBlob.length();
16 }
17 byte[] buffer = new byte[size];
18 int length = -1;
19 while ((length = inStream.read(buffer)) != -1)
20 {
21 outStream.write(buffer, 0, length);
22 outStream.flush();
23 }
24 inStream.close();
25 outStream.close();
26 }
27 }
28 /
Java created.
Elapsed: 00:00:00.16
SQL>
SQL> ALTER JAVA SOURCE "BlobHandler" COMPILE;
Java altered.
Elapsed: 00:00:00.37
SQL>
SQL> sho error
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE ExportBlobJava (p_file IN VARCHAR2,
2 p_blob IN BLOB)
3 AS
4 LANGUAGE JAVA
5 NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';
6 /
Procedure created.
Elapsed: 00:00:00.10
SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> EXEC Dbms_Java.Grant_Permission( 'USER1', 'SYS:java.io.FilePermission','/tmp/javatest', 'write' )
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.52
SQL>
a comment here. If you do care about your database server, and you probably won’t GRANT DBA TO PUBLIC, never grant full unlimited access to your filesystem, as suggested chockingly by metalink with Dbms_Java.Grant_Permission( ‘SCOTT’, ‘java.io.FilePermission’, ‘*’, ‘read ,write, execute, delete’);
let’s move on
SQL> connect user1/SeCrEt
Connected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE ExportBlobPlsql (p_dir IN VARCHAR2,
2 p_file IN VARCHAR2,
3 p_blob IN BLOB)
4 AS
5 blob_length INTEGER;
6 out_file UTL_FILE.FILE_TYPE;
7 v_buffer RAW (32767);
8 chunk_size BINARY_INTEGER := 32767;
9 blob_position INTEGER := 1;
10 BEGIN
11 blob_length := DBMS_LOB.GETLENGTH (p_blob);
12 out_file :=
13 UTL_FILE.FOPEN (p_dir,
14 p_file,
15 'wb',
16 chunk_size);
17 WHILE blob_position <= blob_length
18 LOOP
19 IF blob_position + chunk_size - 1 > blob_length
20 THEN
21 chunk_size := blob_length - blob_position + 1;
22 END IF;
23
24 DBMS_LOB.READ (p_blob,
25 chunk_size,
26 blob_position,
27 v_buffer);
28 UTL_FILE.PUT_RAW (out_file, v_buffer, TRUE);
29 blob_position := blob_position + chunk_size;
30 END LOOP;
31 UTL_FILE.FCLOSE (out_file);
32 END;
33 /
Procedure created.
Elapsed: 00:00:00.08
SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> CREATE OR REPLACE DIRECTORY tmp AS '/tmp';
Directory created.
Elapsed: 00:00:00.03
SQL>
SQL> grant write on directory tmp to user1;
Grant succeeded.
Elapsed: 00:00:00.02
SQL>
Here again, I give write access to one directory, I do not grant dba to public…
SQL> connect user1/SeCrEt
Connected.
SQL>
SQL> VAR c BLOB
SQL>
SQL> exec :c := UTL_RAW.cast_to_raw ('X'); FOR i IN 1 .. 15 LOOP DBMS_LOB.append (:C, :C); END LOOP
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL>
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB FROM DUAL;
KB
----------
32
Elapsed: 00:00:00.06
I have created a 32K BLOB variable, and I am doubling its size each time to see how it scales
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB FROM DUAL;
KB
----------
64
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB FROM DUAL;
KB
----------
128
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB FROM DUAL;
KB
----------
256
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB FROM DUAL;
KB
----------
512
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
1
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
2
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
4
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
8
Elapsed: 00:00:00.01
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
SQL>
For small files, we do not see much of a difference between java and plsql. This is because the 32k buffer of plsql is acceptable for small files.
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
16
Elapsed: 00:00:00.02
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
32
Elapsed: 00:00:00.04
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.31
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.29
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
64
Elapsed: 00:00:00.08
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.45
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.90
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.60
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
128
Elapsed: 00:00:00.14
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.96
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.77
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.08
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
256
Elapsed: 00:00:00.30
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.24
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.65
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:00:34.70
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
512
Elapsed: 00:00:00.70
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.92
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.81
SQL>
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:01:32.96
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
1024
Elapsed: 00:00:01.65
SQL> EXEC ExportBlobJava('/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.90
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:44.13
SQL> exec DBMS_LOB.append (:C, :C)
PL/SQL procedure successfully completed.
Elapsed: 00:03:33.14
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024 MB FROM DUAL;
MB
----------
2048
Elapsed: 00:00:00.00
SQL> EXEC ExportBlobJava('/u99/backup/sw/tmp/javatest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.10
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
PL/SQL procedure successfully completed.
Elapsed: 00:01:16.90
The larger the file, the most benefical to have a large write buffer (could well be higher than 20M as in this example). If java is not installed in the database, you can also retriebe the blob from the database and output the stream to a local file.