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
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.