Search code examples
csvmultilingual

SQLPLUS writes ¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿ to csv file when finds a data in other languages than English

I am trying to export the data from the Oracle table to csv file. The table has one of the column defined as CLOB data type and with data stored in multiple languages.

For instance, 

create table test1
( col1 number,
col2 clob);

Insert into test1 values ( 1, '使用 Excel 数据挖掘外接程序执行购物篮分析');

select * from test1;

Executed and screenshot taken from SQL Developer: image

When I ran the below script from SQLPlus

set echo off
set newpage none
set feedback off
set pages 0
spool C:\Users\venkat\Desktop\data.csv
select 'col1,col2' from dual;
select col1||',' || col2
from test1; 
spool off;

The data in the file appears like this

col1,col2
1,¿¿ Excel ¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿

Looking forward for some help in this forum. I want to see the data as it is in the database.

Edit: Apologies, I should have mentioned Environment as Windows.

More information on this NLS language parameter here

Select * from nls_database_parameters
 where parameter IN ('NLS_LANGUAGE','NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

And the result of the says

NLS_LANGUAGE AMERICAN NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

Solution

  • You must set the NLS_LANG environment variable in order to tell SQLPlus what character encoding to use; specify a character encoding which can represent the expected characters. See for example "Running SQLPlus with bash causes wrong encoding" on Server Fault.

    In Bash you would set an environment variable like this:

     export NLS_LANG="AMERICAN_AMERICA.UTF8"

    On Windows with cmd.exe you would say

     set NLS_LANG=AMERICAN_AMERICA.UTF8