How to export/unload an Oracle BLOB large
object
By Laurent Schneider
This article describes how to export and unload a large object
(BLOB, CLOB) from a database table into a flat file.
There is more than one post on how to unload blob from the
database, mostly in PL/SQL with utl_file.put_raw and with Java with
FileOutputStream.
Unfortunately both ways of
exporting a LOB are 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.
Using Java to export a BLOB/CLOB
Using java for exporting a blob (read large binary large object) is
about 3x faster than using PL/SQL in my test.
SQL> CREATE USER USER1
IDENTIFIED BY SeCrEt;
SQL> GRANT CREATE SESSION , CREATE PROCEDURE
TO USER1;
SQL>
connect user1/SeCrEt
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 /
SQL> ALTER JAVA SOURCE "BlobHandler" COMPILE;
SQL> sho error
No errors.
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 /
SQL> connect
/ as sysdba
SQL>
EXEC Dbms_Java.Grant_Permission( 'USER1', 'SYS:java.io.FilePermission','/tmp/javatest',
'write' )
Next, we start
unloading the CLOB/BLOB:
SQL> connect user1/SeCrEt
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 /
SQL> connect / as sysdba
SQL> CREATE OR REPLACE DIRECTORY tmp AS '/tmp';
SQL> grant write on directory tmp to user1;
Here again, I give write access to one directory:
SQL> connect user1/SeCrEt
SQL> VAR c BLOB
SQL> exec :c := UTL_RAW.cast_to_raw ('X'); FOR
i IN 1 .. 15 LOOP DBMS_LOB.append (:C, :C); END LOOP
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB
FROM DUAL;
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)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB
FROM DUAL;
SQL> EXEC
ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB
FROM DUAL;
SQL> EXEC
ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB
FROM DUAL;
SQL> EXEC
ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024 KB
FROM DUAL;
SQL> EXEC
ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
For small files, we do not see much of a difference between
the export speed using Java and PL/SQL. This is because the 32k buffer of
PL/SQL is
acceptable for small files.
Below, I run ExportBlobJava to unload the CLOB/BLOB from
Oracle:
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
SQL> exec DBMS_LOB.append (:C, :C)
SQL> SELECT DBMS_LOB.getlength (:c)/1024/1024
MB FROM DUAL;
SQL>
EXEC ExportBlobJava('/u99/backup/sw/tmp/javatest',:c)
SQL> EXEC ExportBlobPlsql('TMP','/plsqltest',:c)
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
