Difference between revisions of "Insert UNICODE character into non-Unicode Oracle database"
Jump to navigation
Jump to search
(Created page with "* To an Unicode database <pre> SQL> select * from v$nls_parameters where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); PARAMETER VALUE -------------------...") |
|||
(2 intermediate revisions by the same user not shown) | |||
Line 12: | Line 12: | ||
$ export NLS_LANG=AMERICAN_AMERICA.UTF8 | $ export NLS_LANG=AMERICAN_AMERICA.UTF8 | ||
− | SQL> desc | + | SQL> desc ic_test_varchar |
Name Null? Type | Name Null? Type | ||
Line 18: | Line 18: | ||
A VARCHAR2(1000) | A VARCHAR2(1000) | ||
− | SQL> insert into | + | SQL> insert into ic_test_varchar values('こんにちは ワールド'); |
SQL> commit; | SQL> commit; | ||
− | SQL> select * from | + | SQL> select * from ic_test_varchar; |
A | A | ||
Line 42: | Line 42: | ||
-- At Unix prompt | -- 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 NLS_LANG=AMERICAN_AMERICA.UTF8 | ||
$ export ORA_NCHAR_LITERAL_REPLACE=TRUE | $ export ORA_NCHAR_LITERAL_REPLACE=TRUE | ||
− | SQL> desc | + | SQL> desc ic_test_n_varchar |
Name Null? Type | Name Null? Type | ||
Line 51: | Line 75: | ||
A NVARCHAR2(1000) | A NVARCHAR2(1000) | ||
− | SQL> insert into | + | -- 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> commit; | ||
− | SQL> select * from | + | SQL> select * from ic_test_n_varchar; |
A | A |
Latest revision as of 17:08, 5 March 2013
- 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 -------------------------------------------------------------------------------- こんにちは ワールド