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
--------------------------------------------------------------------------------
こんにちは ワールド