Extract BLOB into Files

An alternative to this solution, you can also use a Java Store Procedure that writes the files, you can find the code to do this on Metalink, check Note:247546.1

Here is a simple sample of how to extract documents from the DB into the file system.

In this particular case, I'm using the Portal documents table, by default, whenever you submit any document to the portal DAD they are store in this table, they are associated with your session, and when your session is over, Portal does regular clean ups to delete any unclaimed documents, usually when you want to keep the document, you can simple set the session value on the table to null, or you can move the document to your own tables as a blob field.

anyway, regardless of where your document is, this procedure should help you extract it into the local file system of the database

First, you need to create the folder in the local file system of the DB server where you want to put you files, make sure that the oracle OS user has write priviledges on the folder.

cd /tmp

mkdir my_db_folder

chmod 777 my_db_folder

After you have created the folder in the file system, you need to create a DB directory and associate it with the OS folder, usually you will do this as sys or system user, also you need to grant access to the folder so the user/schema that you are going to use to run the procedure, can write to it.

sqlplus "/ as sysdba"

SQL> CREATE DIRECTORY MY_FOLDER AS '/tmp/my_db_folder';

SQL> GRANT READ,WRITE ON DIRECTORY MY_FOLDER TO MY_SCHEMA

Now you create the procedure, it receives the file name as it is store in the portal.WWDOC_DOCUMENT$ table, you can change that to recieve the ID of your custom table, also it receives a display_name, this will be the name of the file in the file system.

create or replace PROCEDURE blob2file(pfname VARCHAR2, display_name in varchar2) IS

vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
v_name varchar2(100);
lv_str_len NUMBER;
l_output utl_file.file_type;

BEGIN
-- define output directory
lv_str_len := length(pfname);
--v_name := display_name||upper(substr(pfname,lv_str_len-3,lv_str_len));
v_name := display_name;
l_output := utl_file.fopen('MY_FOLDER', v_name, 'w', 32760);

-- get length of blob
SELECT dbms_lob.getlength(blob_content)
INTO len
FROM portal.WWDOC_DOCUMENT$
WHERE FILENAME = pfname;


-- dbms_output.put_line('Length: '||len);
-- save blob length
x := len;

-- select blob into variable
SELECT blob_content
INTO vblob
FROM portal.WWDOC_DOCUMENT$
WHERE FILENAME = pfname;

-- if small enough for a single write
IF len < 32760 THEN
-- dbms_output.put_line('Single write ');
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
-- dbms_output.put_line('multi write '||vstart);
vstart := 1;
WHILE vstart < len
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 LOOP;
END IF;
dbms_output.put_line('End');
utl_file.fclose(l_output);
EXCEPTION
when others then dbms_output.put_line('ERROR:'||entry_id);
END blob2file;