5

Having problem loading text files into database as clob.

  • Oracle version: Oracle Database 11g EE Release 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!

someuser
  • 83
  • 1
  • 1
  • 6
  • "_I run select and get squares_" -- this probably just means that the client you're using to execute the query is unable to display Cyrillics. – mustaccio Jul 20 '13 at 13:06
  • 1
    @mustaccio, but varchar2 columns with cyrillics displayed properly – someuser Jul 20 '13 at 13:23

1 Answers1

4

Try LOADCLOBFROMFILE instead of 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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • 1
    when using loadfromfile clob data was on one line, when using loadfromclobfile structure of text became is in original text file with end of lines etc but still with "squares". Dont know if sql*loader comes by default with client or not, but I cant install anything than what is on my work pc. – someuser Jul 20 '13 at 17:43