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
$ 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 $ 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

---

こんにちは ワールド