Writing Oracle BLOB to OS file

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Writing Oracle BLOB to OS file

Oracle Database Tips by Burleson Consulting

It's easy to write a BLOB (CLOB) to an OS file using PL/SQL.

Note that the PL/SQL below uses dbms_lob.read to read the BLOB or CLOB from Oracle, and utl_file.put_raw to write the blob to the file.

For more details on writing advanced PL/SQL, see Dr. Halls book "PL/SQL Tuning".

Here is non-working psuedocode that demonstrates the concept:

CREATE OR REPLACE PROCEDURE extract_file(product_id in number) IS

vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;

l_output utl_file.file_type;

BEGIN

-- define output directory
l_output := utl_file.fopen('DIR_TEMP', 'filename','wb', 32760);

vstart := 1;
bytelen := 32000;

-- get length of blob
SELECT dbms_lob.getlength(productblob)
INTO len
FROM products
WHERE id = product_id;

-- save blob length
x := len;

-- select blob into variable
SELECT product_blob
INTO vblob
FROM products
WHERE id = product_id;

-- if small enough for a single write
IF len < 32760 THEN
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
vstart := 1;
WHILE vstart < len and bytelen > 0
LOOP
   dbms_lob.read(vblob,bytelen,vstart,my_vr);

   utl_file.put_raw(l_output,my_vr);
   utl_file.fflush(l_output);

   -- set the start position for the next cut
   vstart := vstart + bytelen;

   -- set the end position if less than 32000 bytes
   x := x - bytelen;
   IF x < 32000 THEN
      bytelen := x;
   END IF;
END IF;
utl_file.fclose(l_output);
end loop;

��