从 Oracle 数据库中提取 BLOB 数据并转换为原始文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18341573/
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
Extract BLOB data from Oracle Database and convert to original file
提问by Brock Davis
I am trying to extract BLOBdata and convert it to the original file (pdf, rtf, doc, etc). Below is my code:
我正在尝试提取BLOB数据并将其转换为原始文件（pdf、rtf、doc 等）。下面是我的代码：
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 file_contents
INTO l_blob
FROM irb_files
WHERE FILE_NAME = 'STD_FlipBook_Religion2013.doc';
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('IRB','STD_FlipBook_Religion2013.doc','wb','W');
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;
-- Close the file.
UTL_FILE.fclose(l_file);
END;
I am relatively new to all of this so I am sorry if my code is a bit off. The error I keep getting is:
我对所有这些都比较陌生，所以如果我的代码有点不对，我很抱歉。我不断收到的错误是：
Error report:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 18
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
Any help would be greatly appreciated.
任何帮助将不胜感激。
Database Info: Oracle9i Enterprise Edition Release 9.2.0.5.0
数据库信息：Oracle9i 企业版 9.2.0.5.0 版
回答by OldProgrammer
The problem is with this line:
问题出在这一行：
l_file := UTL_FILE.fopen('IRB','STD_FlipBook_Religion2013.doc','wb','W');
The docs show the signature of the function below. The last parameter you have, 'W', corresponds to the 'max_linesize', which is expected to be a number. So, you cannot convert 'W' to a number. I think you can simply use the default value in your case, so just remove the 'W' parameter.
文档显示了下面函数的签名。您拥有的最后一个参数“W”对应于“max_linesize”，它应该是一个数字。因此，您不能将 'W' 转换为数字。我认为您可以简单地在您的情况下使用默认值，因此只需删除 'W' 参数。
(from Oracle 9.2 docs)
（来自 Oracle 9.2 文档）
FOPEN Function This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously. See also "FOPEN_NCHAR Function".
FOPEN 函数 此函数打开一个文件。您可以指定最大行大小并同时打开最多 50 个文件。另请参见“FOPEN_NCHAR 函数”。
Syntax UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type;
语法 UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type;
Parameters Table 95-3 FOPEN Function Parameters Parameter Description location Directory location of file.
参数 表95-3 FOPEN 功能参数 参数 说明 location 文件的目录位置。
filename File name, including extension (file type), without directory path. In Unix, the filename cannot end with /.
filename 文件名，包括扩展名（文件类型），不包括目录路径。在 Unix 中，文件名不能以 / 结尾。
open_mode Specifies how the file is opened. Modes include:
open_mode 指定文件的打开方式。模式包括：
r--read text
r--阅读文本
w--write text
w--写文本
a--append text
a--追加文本
If you try to open a file that does not exist using a value for open_mode, then the file is created in write mode.
如果您尝试使用 open_mode 的值打开一个不存在的文件，则会在写入模式下创建该文件。
max_linesize Maximum number of characters per line, including the newline character, for this file. (minimum value 1, maximum value 32767). The default is approximately 1000 bytes.
max_linesize 此文件每行的最大字符数，包括换行符。（最小值 1，最大值 32767）。默认值约为 1000 字节。
