Here is a test case:
create table mydatabase.samuel(toto varchar(10));
insert into mydatabase.samuel values('¿¿)');
select toto, dump(toto) from mydatabase.samuel;
and the ouput is : ¿¿) Typ=1 Len=5: 194,191,194,191,41
When I try to export this result to a file I do the following:
set TERMOUT OFF
set timing off
set pagesize 0
set colsep ';'
set feedback off
set linesize 1000
set trimspool on
ALTER SESSION SET NLS_LANGUAGE='FRENCH';
ALTER SESSION SET NLS_TERRITORY='FRANCE';
ALTER SESSION SET NLS_CURRENCY='€';
ALTER SESSION SET NLS_ISO_CURRENCY='FRANCE';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=', ';
ALTER SESSION SET NLS_CALENDAR='GREGORIAN';
ALTER SESSION SET nls_date_format='dd/mm/yyyy hh24:mi:ss';
ALTER SESSION SET NLS_DATE_LANGUAGE='FRENCH';
ALTER SESSION SET NLS_CHARACTERSET='AL32UTF8';
ALTER SESSION SET NLS_SORT='FRENCH';
ALTER SESSION SET NLS_TIME_FORMAT='HH24:MI:SSXFF';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD/MM/YYYY HH24:MI:SSXFF';
ALTER SESSION SET NLS_TIME_TZ_FORMAT='HH24:MI:SSXFF TZR';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD/MM/YYYY HH24:MI:SSXFF TZR';
ALTER SESSION SET NLS_DUAL_CURRENCY='€';
ALTER SESSION SET NLS_NCHAR_CHARACTERSET='AL16UTF16';
ALTER SESSION SET NLS_COMP='BINARY';
ALTER SESSION SET NLS_LENGTH_SEMANTICS='BYTE';
ALTER SESSION SET NLS_NCHAR_CONV_EXCP='FALSE';
spool /mydir/myfile.csv
SELECT toto || ';' || dump(toto) FROM mydatabase.samuel;
spool off;
exit
I got the NLS values by doing SELECT * FROM V$NLS_PARAMETERS;
And the result is ??);Typ=1 Len=5: 194,191,194,191,41 despite the NLS_CHARACTERSET='AL32UTF8'.
How can I fix the encoding of the export?