Oracle UTF8 File Encoding With Utl_file

Question

I want to export my file as UTF8 encoding. When I checked v$nls_parameters, nls_characterset is WE8ISO8859P9. So encoding of my file is Ansii.

How can I export my file with UTF8 encoding without changing v$nls_parameters?

DECLARE
 v_os_touch_file                  utl_file.file_type;
 p_in_file                        VARCHAR2(50);
BEGIN
    v_os_touch_file := NULL;
    p_in_file := NULL;

    p_in_file := 'my_file_' || sysdate;
    v_os_touch_file := utl_file.fopen(my_path, p_in_file, 'w');
    FOR i IN (
        SELECT
            *
        FROM
            my_table
    ) LOOP
    begin

        utl_file.put_line(v_os_touch_file, 'TEST' );
        utl_file.put_line(v_os_touch_file, i.input);
         utl_file.fclose(v_os_touch_file);
END

Answer 1

Use UTL_FILE.FOPEN_NCHAR

Even though the contents of an NVARCHAR2 buffer may be AL16UTF16 or UTF8 (depending on the national character set of the database), the contents of the file are always read and written in UTF8. UTL_FILE converts between UTF8 and AL16UTF16 as necessary.

Note, using p_in_file := 'my_file_' || sysdate; is not very smart, because it relies on current user session NLS_DATE_FORMAT setting. Imagine the default format is MM/DD/YYYY.

Better use like p_in_file := 'my_file_' || TO_CHAR(sysdate, 'YYYYMMDD'); for example.