Insert UNICODE character into non-Unicode Oracle database


 * To an Unicode database

SQL> select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER		      VALUE -- -- NLS_CHARACTERSET	      AL32UTF8 NLS_NCHAR_CHARACTERSET	      AL16UTF16

-- At Unix prompt $ export NLS_LANG=AMERICAN_AMERICA.UTF8

SQL> desc ic_test_varchar

Name					  Null? Type -  A						    VARCHAR2(1000)

SQL> insert into ic_test_varchar values('こんにちは ワールド');

SQL> commit;

SQL> select * from ic_test_varchar;

A

こんにちは ワールド


 * To Non-Unicode database

SQL> select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER		      VALUE -- -- NLS_CHARACTERSET	      WE8MSWIN1252 NLS_NCHAR_CHARACTERSET	      AL16UTF16

-- At Unix prompt -- Case #1 - Not using NCHAR literal - Not working $ export NLS_LANG=AMERICAN_AMERICA.UTF8

SQL> desc ic_test_n_varchar

Name					  Null? Type -  A						    NVARCHAR2(1000)

SQL> insert into ic_test_n_varchar values('こんにちは ワールド');

SQL> commit;

SQL> select * from ic_test_n_varchar;

A

¿¿¿¿¿ ¿¿¿¿

-- Case #2 - Using NCHAR literal - Working $ export NLS_LANG=AMERICAN_AMERICA.UTF8 $ export ORA_NCHAR_LITERAL_REPLACE=TRUE

SQL> desc ic_test_n_varchar

Name					  Null? Type -  A						    NVARCHAR2(1000)

-- Use NCHAR literal -- http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements003.htm#i42617

SQL> insert into ic_test_n_varchar values(N'こんにちは ワールド');

SQL> commit;

SQL> select * from ic_test_n_varchar;

A

こんにちは ワールド