I have a database which has a number of files stored in a BLOB field.
How can I extract & save the original files? There are many different file types - doc, pdf, xls, etc. The table has the extension in one col, and the original file name in another. There may be multiple files with the same file name, too.
Enter the connection details to connect to your database server. Select the table you want to export your blobs from. Tables containing blob columns are highlighted in green. You can also enter a SQL command to select the blobs you want to export.
Select the BLOB column from the Export this column drop-down list. Then specify the export location in the Export Path field. Then select the radio button Export to files named for the value in this column (if the file names stored in a column), else choose sequentially numbered files option.
You can use the UTL_FILE package to do this in version 9i onwards
something like this:
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos NUMBER := 1;
l_blob BLOB;
l_blob_len NUMBER;
BEGIN
SELECT blobcol
INTO l_blob
FROM table
WHERE rownum = 1;
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen(<location>,<filename>,'wb', 32767);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
END;
/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With