如何从Oracle BLOB字段提取文件? [英] How can I extract files from an Oracle BLOB field?

问题描述

我有一个数据库，该数据库的BLOB字段中存储了许多文件.

I have a database which has a number of files stored in a BLOB field.

我该如何提取&保存原始文件?有许多不同的文件类型-doc，pdf，xls等.该表在一个列中具有扩展名，而在另一列中具有原始文件名.可能还会有多个文件具有相同的文件名.

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.

推荐答案

您可以使用

You can use the UTL_FILE package to do this in version 9i onwards

类似这样的东西:

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;
/

这篇关于如何从Oracle BLOB字段提取文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！