UTL_FILE and character set

Question

I have been working on this thing for several days and it drives me crazy.
I have an oracle procedure that writes a file using UTL_FILE. I used to store my values ​​as NVARCHAR2 and wrote my file using the procedure UTL_FILE.PUT_LINE_NCHAR, and he wrote the file to (which notepad ++ treats as) UTF8.
Then the file is used by another program, the problem is that the specified program reads it using WE8MSWIN1252, and I can not change it, because it is an outdated code.
So I tried using the procedure UTL_FILE.PUT_LINE, but the file was still considered UTF8. I saw in the oracle documentation that I NVARCHAR2used the national character set (my AL16UTF16), so I tried to use the method CONVERTas follows:

CONVERT(whatIWantToWrite, 'WE8MSWIN1252', 'AL16UTF16'))

and he raised the ORA-29298 Exception character set mismatch. I do not understand, mine NLS_NCHAR_CHARACTERSETis AL16UTF16, why can not I convert it to WE8MSWIN1252?
Is there a way to write a file using WE8MSWIN1252?

Answer 1

It is like you are still opening the file with fopen_nchar. If I do this:

create table t42(str nvarchar2(20));
insert into t42 values ('Hello');

declare
  file utl_file.file_type;
  l_str nvarchar2(20);
begin
  select str into l_str from t42;
  file := utl_file.fopen('<directory>', 'dummy.dat', 'w', 32767);
  utl_file.put_line(file, convert(l_str, 'WE8MSWIN1252', 'AL16UTF16'));
  utl_file.fclose(file);
end;
/

... then I get a file containing 䡥汬that the Linux command filereports as UTF-8 Unicode text; Notepad ++ shows 䡥汬and says that the file is ANSI as UTF-8.

If I changed the value fopento fopen_nchar:

  file := utl_file.fopen_nchar('CENSYS_EXPORT_DIR', 'dummy.dat', 'w', 32767);

... then I get ORA-29298: Character set mismatchand an empty file.

fopen, PL/SQL varchar2:

declare
  file utl_file.file_type;
  l_str varchar2(20);
begin
  select str into l_str from t42;
  file := utl_file.fopen('<directory>', 'dummy.dat', 'w', 32767);
  utl_file.put_line(file, convert(l_str, 'WE8MSWIN1252', 'AL16UTF16'));
  utl_file.fclose(file);
end;
/

... ¿¿ ( vim), ISO-8859 text. Notepad ++ ߿ , ANSI.

, convert, Oracle , raw:

declare
  file utl_file.file_type;
  l_str varchar2(20);
begin
  select str into l_str from t42;
  file := utl_file.fopen('<directory>', 'dummy.dat', 'w', 32767);
  utl_file.put_line(file,
    utl_raw.cast_to_varchar2(utl_raw.convert(utl_raw.cast_to_raw(l_str),
      'ENGLISH_UNITED KINGDOM.WE8MSWIN1252', 'ENGLISH_UNITED KINGDOM.UTF8')));
  utl_file.fclose(file);
end;
/

Linux, Hello, ASCII text; Notepad ++ Hello, , ANSI. , , ... , , .

- AL32UTF8, - AL16UTF16, ; WE8MSWIN1252, ; :

UTL_FILE , , UTL_FILE.FOPEN . , UTL_FILE.FOPEN_NCHAR , UTF8 .

Answer 2

, , , . Java Native-to-ASCII.

native2ascii -encoding UTF8 my_text_file_utf.txt my_text_file.tmp
native2ascii -reverse -encoding windows-1252 my_text_file.tmp my_text_file_1252.txt

Answer 3

dbms_xslprocessor.clob2file.

declare
  l_str varchar2(20);
BEGIN
  select str into l_str from t42;
  dbms_xslprocessor.clob2file(to_clob(l_str), 'UTLDIR', 'file.txt', 2000);
END;

AL16UTF16 (csid) = 2000 WE8MSWIN1252 (csid) = 178 CSID:

SELECT NLS_CHARSET_ID('WE8MSWIN1252') FROM DUAL;