如何从 Oracle BLOB 字段中提取文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6332032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I extract files from an Oracle BLOB field?
提问by chris
I have a database which has a number of files stored in a BLOB field.
我有一个数据库，其中有许多文件存储在 BLOB 字段中。
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.
如何提取和保存原始文件？有许多不同的文件类型 - doc、pdf、xls 等。表格的扩展名在一个列中，原始文件名在另一个列中。也可能有多个文件具有相同的文件名。
回答by StevieG
You can use the UTL_FILEpackage to do this in version 9i onwards
您可以使用UTL_FILE包在版本 9i 中执行此操作
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;
/
