Oracle Unicode Spooling

Question

How can I spool data from a table to a file which contains Unicode characters?

I have a sql file which I execute from SQL*Plus screen and its content is:

SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SPOOL STREET_POINT_THR.BQSV
SELECT GEOX||'`'||GEOY||'`'||UNICODE_DESC||'`'||ASCII_DESC 
FROM GEO.STREET_POINTS;
SPOOL OFF

Answer 1

with the right settings your script does work with SQL*Plus. Here is what I did to test it:

• (obviously) your database must support unicode. Use NVARCHAR2 if necessary.
• Setup your client application correctly. make sure your NLS_LANG variable is set correctly, it must support unicode. I set mine to AMERICAN_ENGLISH.UTF8. While the DOS window of SQL*Plus won't display all unicode characters, they will be spooled correctly into the file.
• (obviously too) make sure the application that reads the spooled file opens it in the right character set.

Now for the script:

SQL> select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER          VALUE
------------------ ------
NLS_CHARACTERSET   UTF8

SQL> create table street_points (data varchar2(10));

Table created

SQL> INSERT INTO street_points VALUES (chr(53401)||chr(53398));

1 row inserted

This will insert the russian characters ЙЖ

SQL> SPOOL STREET_POINT_THR.BQSV
SQL> SELECT * FROM STREET_POINTS;
ðÖðû
SQL> SPOOL OFF

The file, opened with a text editor (jEdit in my case) with the correct character set (UTF-8) displays the characters correctly.