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
$ export NLS_LANG=AMERICAN_AMERICA.UTF8
$ export ORA_NCHAR_LITERAL_REPLACE=TRUE

SQL> desc ic_test_n_varchar

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 A						    NVARCHAR2(1000)

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

SQL> commit;
 

SQL> select * from ic_test_n_varchar;

A
--------------------------------------------------------------------------------
こんにちは ワールド