oracle 将文本文件作为 clob 加载到数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17760692/
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
Load text files as clob to database
提问by someuser
Having problem loading text files into database as clob.
将文本文件作为 clob 加载到数据库时出现问题。
- Oracle version: Oracle Database 11g EE Release 11.2.0.2.0
- NLS_LANG: RUSSIAN_RUSSIA.CL8MSWIN1251
- NLS_CHARACTERSET: AL32UTF8
- Oracle 版本：Oracle 数据库 11g EE 版本 11.2.0.2.0
- NLS_LANG：RUSSIAN_RUSSIA.CL8MSWIN1251
- NLS_CHARACTERSET: AL32UTF8
code to load text files:
加载文本文件的代码：
DECLARE
L_BFILE BFILE;
L_CLOB CLOB;
file_name VARCHAR2(300);
BEGIN
file_name := 'test.txt';
L_BFILE := BFILENAME('DIR', file_name);
if (dbms_lob.fileexists(l_bfile) = 1) then
INSERT INTO TEST T
VALUES (SEQ_TEST.NEXTVAL, EMPTY_CLOB(),file_name) return r_data into l_clob;
L_BFILE := BFILENAME('DIR', file_name);
DBMS_LOB.FILEOPEN(L_BFILE, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE(L_CLOB, L_BFILE, DBMS_LOB.GETLENGTH(L_BFILE));
DBMS_LOB.FILECLOSE(L_BFILE);
COMMIT;
end if;
END;
Text files are UTF8. After loading into database, I run select and get squares instead of russian characters. Please help!
文本文件是UTF8。加载到数据库后，我运行 select 并获取方块而不是俄语字符。请帮忙！
采纳答案by Jon Heller
Try LOADCLOBFROMFILEinstead of LOADFROMFILE.
尝试LOADCLOBFROMFILE而不是LOADFROMFILE。
From the manual:
从手册：
Note: If the character set is varying width, UTF-8 for example, the LOB value is stored in the fixed-width UCS2 format. Therefore, if you are using DBMS_LOB.LOADFROMFILE, the data in the BFILE should be in the UCS2 character set instead of the UTF-8 character set. However, you should use sql*loader instead of LOADFROMFILE to load data into a CLOB or NCLOB because sql*loader provides the necessary character set conversions.
注意：如果字符集宽度不同，例如 UTF-8，则 LOB 值以固定宽度的 UCS2 格式存储。因此，如果您使用 DBMS_LOB.LOADFROMFILE，则 BFILE 中的数据应该是 UCS2 字符集而不是 UTF-8 字符集。但是，您应该使用 sql*loader 而不是 LOADFROMFILE 将数据加载到 CLOB 或 NCLOB，因为 sql*loader 提供了必要的字符集转换。
