Load text files as clob to database
13,817
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.
Author by
Admin
Updated on June 27, 2022Comments
-
Admin almost 2 years
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!