0

Currently I'm working on Oracle database and want to create a table schema that can accept Unicode character with below query. When i insert the characters they inserted successfully but when i select from table it results as ????. The characters are in Urdu language. As per my requirement i have to create a table schema which can accept English and Urdu characters at the same time. Please help

CREATE TABLE product_information 
( product_id          NUMBER(6) 
, product_name        NVARCHAR2(100) 
, product_description VARCHAR2(1000));
INSERT INTO product_information 
(product_id,product_name,product_description) VALUES (10,'آسان','طاقت')

Unicode table query

Database Characterset

Asad ch
  • 47
  • 1
  • 7
  • Possible duplicate of [How can I tell if my Oracle system is set to support Unicode or multibyte characters?](https://stackoverflow.com/questions/9703421/how-can-i-tell-if-my-oracle-system-is-set-to-support-unicode-or-multibyte-charac) – Mohammad Aug 01 '18 at 17:32
  • It may be that the query tool cannot render those characters in the font used in the result grid. Try querying select asciistr(product_name), asciistr(product_description) to determine what characters are actually in the data. – Gary Myers Aug 02 '18 at 02:40

1 Answers1

0

Unicode gets stored in NCHAR and NVARCHAR character but while selecting it will return ??? if NLS_CHARACTERSET used for normal CHAR and NVARCHAR is not UTF8 encoded. We solve this issue by converting the character set of the database(NLS CHARACTERSET and NLS_NCHAR_CHARACTERSET to AL16UTF16) using DMU tool provided by Oracle. Its very handy tool and take care of character set migration. You will need to have a backup and their need to reserve down as this activity required database restart. Once restarted you will be able to view UNICODE.For reference. https://docs.oracle.com/database/121/DUMAG/ch1overview.htm#DUMAG105

Bhanu Yadav
  • 169
  • 7
  • NLS_NCHAR_CHARACTERSET is already AL16UTF16, Do we need to only convert NLS CHARACTERSET to AL32UTF8 ----> AL16UTF16? – Asad ch Aug 01 '18 at 18:35
  • have you used DMU and restarted the database.We have used DMU and restated the database post which we are able to store multi-ligual characters The benefits of UTF-16 over UTF-8 are as follows: More compact storage for Asian scripts because most of the commonly used Asian characters are represented in two bytes. https://docs.oracle.com/database/121/NLSPG/ch6unicode.htm#GUID-98BD4E64-99AE-4AD8-8CAD-EE438D988318 – Bhanu Yadav Aug 01 '18 at 18:50