Insert UNICODE character into non-Unicode Oracle database
Jump to navigation
Jump to search
- 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
こんにちは ワールド