oracle - 将文本文件作为 clob 加载到数据库

Question

将文本文件作为 clob 加载到数据库时出现问题。

Oracle 版本:Oracle 数据库 11g EE 版本 11.2.0.2.0

NLS_LANG:RUSSIAN_RUSSIA.CL8MSWIN1251

NLS_CHARACTERSET: AL32UTF8

加载文本文件的代码:

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;

文本文件是UTF8。加载到数据库后，我运行 select 并获取方块而不是俄语字符。请帮忙!

Answer 1

试试 LOADCLOBFROMFILE而不是 LOADFROMFILE .

从手册:

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.