本文介绍了如何从Oracle BLOB字段提取文件?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个数据库,该数据库的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.

推荐答案

您可以使用 UTL_FILE 软件包即可在版本9i及更高版本中执行此操作

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字段提取文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-19 09:49
查看更多