Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I extract files from an Oracle BLOB field?

Tags:

file

oracle

blob

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.

like image 636
chris Avatar asked Jun 13 '11 14:06

chris


People also ask

How do I extract a BLOB file?

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.

How do I export my BLOB data?

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.


1 Answers

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;
/
like image 74
StevieG Avatar answered Oct 16 '22 14:10

StevieG