UTL_FILE and character set

character-encoding, oracle, plsql

Solution

This seems to be because you're 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 `䡥汬`, which the Linux `file` command reports as `UTF-8 Unicode text`; Notepad++ shows `䡥汬` and says the file is 'ANSI as UTF-8'.

If I change the `fopen` to `fopen_nchar`:

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

... then I get `ORA-29298: Character set mismatch` and an empty file.

If I go back to `fopen` but change the PL/SQL variable to `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;
/

... then the file contains `¿¿` (in `vim`) and the file is reported as `ISO-8859 text`. But Notepad++ shows `߿` and says the file is ANSI.

Rather than using `convert`, which Oracle discourages, you can bounce it through 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;
/

In Linux that shows as `Hello` and the file is reported as `ASCII text`; Notepad++ shows it as `Hello` as well, and again says the file is ANSI. I'm unclear if that gets you where you need to be... and you might need a different language and locale, of course.

But my database character set is AL32UTF8, and my national character set is AL16UTF16, so you might see different behaviour; if your database character set is WE8MSWIN1252 then the file will be created as that as well; from the documentation:

`UTL_FILE` expects that files opened by `UTL_FILE.FOPEN` in text mode are encoded in the database character set. It expects that files opened by `UTL_FILE.FOPEN_NCHAR` in text mode are encoded in the UTF8 character set.

Problem

I've been working on this thing for days and it's driving me crazy. I have an oracle procedure that write a file using `UTL_FILE`. I used to store my values as NVARCHAR2 and write my file using `UTL_FILE.PUT_LINE_NCHAR` procedure, and it wrote file in (what notepad++ consider as) UTF8. The file is then used by another program, the problem is that said program read it using WE8MSWIN1252, and I can't change that, since it's legacy code. So I tried to use `UTL_FILE.PUT_LINE` procedure instead, but the file was still considered as UTF8. I saw in oracle's documentation that `NVARCHAR2` used the national character set (mine is AL16UTF16), so I tried to use the `CONVERT` method like this: ``` CONVERT(whatIWantToWrite, 'WE8MSWIN1252', 'AL16UTF16')) ``` and it raised the ORA-29298 Character set mismatch Exception. I don't get it, my `NLS_NCHAR_CHARACTERSET` is AL16UTF16 why can't I convert it to WE8MSWIN1252 ? Is there another way to write a file using WE8MSWIN1252 ?

Original source