For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Yes, I would advise a Unicode DB - don't create a separate one - you main DB should be using unicode..
Just insert your unicode characters from whatever application layer you have (Java?)
One thing to note - use varchar2(20 char) rather than varchar2(20) as that
defaults to 20 bytes not 20 characters.
Thank you for time and Reply dear Paul.
As you suggested
so that i can able to view the french data only in application side .
can i able to view the data in tool we are using to communicate oracle (sql developer,Toad)
if I select query can i able to view french data in sql developer.
Please help.
I'm not a sql developer or toad user but I believe unicode is viewable in those.
But that's just a client-side issue. The important thing is to get
the DB to be able to handle non-ascii characters.
Thanks again for your Reply
From database end we need to have
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
and create the table by SQL NCHAR() to store data in Unicode
CREATE TABLE product_information
( product_id NUMBER(6)
, product_name NVARCHAR2(100)
, product_description VARCHAR2(1000));
Now,please help how my database Identifies this unicode value is for French Or German language.?
Should it handled in Frontend?
You need not to create new database or even change your database character set. Just use nvarchar2 instead of varchar2 data type to store language other than English.
Hi Ricky,
please suggest how to handle multilingual in oracle?
correct character set for multilingual database is al32utf8 like Paul suggested.
Not sure you asked this from data perspective but i'll tell my views anyway.
Multilingual data can be in vertical or horizontal or code based (means lookups).
Here's very simple demo how multilingual product data can be inserted and selected through a view that depends on context that is set by application. That context can be custom made, but in this demo i used built-in userenv context lang value that is derived from session nls_language. That way application does not have to code where product_lang = current or pick columns depending on current lang either in application code or worse by dynamic sql.
Demo is not illustration of "the only way" or ideal data model but gives an idea for vertical single product data. That has many product rows for same id but name in different language. Horizontal data would be one product row but name column for every language. Code based would define product names as key - value - lang list to lookup from code/dimension table and key would then be in product.product_name.
All mentioned models cause some kind design problems that you need to mitigate by data model and application logic that is best choice* for current requirements.
*) efficient to query, easy to maintain, consistent data by constraints
create table t_product ( product_id number not null, product_lang varchar2(3) default sys_context('userenv','lang') not null, product_name varchar2(20) not null, constraint un_product unique(product_id, product_lang) ) ; create or replace view product as select product_id, product_lang, product_name from t_product where product_lang = sys_context('userenv','lang') ; alter session set nls_language=english ; insert into product(product_id, product_name) values(1, 'Product 1 name GB') ; insert into product(product_id, product_name) values(2, 'Product 2 name GB') ; select product_id, product_name from product ; alter session set nls_language=french ; insert into product(product_id, product_name) values(1, 'Product 1 name F') ; insert into product(product_id, product_name) values(2, 'Product 2 name F') ; select product_id, product_name from product; alter session set nls_language=swedish ; insert into product(product_id, product_name) values(1, 'Product 1 name S') ; insert into product(product_id, product_name) values(2, 'Product 2 name S') ; select product_id, product_name from product ; select * from t_product ; drop table t_product purge ; drop view product ; Table T_PRODUCT created. View PRODUCT created. Session altered. 1 row inserted. 1 row inserted. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name GB 2 Product 2 name GB Session altered. 1 row inserted. 1 row inserted. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name F 2 Product 2 name F Session altered. 1 row inserted. 1 row inserted. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name S 2 Product 2 name S PRODUCT_ID PRO PRODUCT_NAME ---------- --- -------------------- 1 GB Product 1 name GB 2 GB Product 2 name GB 1 F Product 1 name F 2 F Product 2 name F 1 S Product 1 name S 2 S Product 2 name S 6 rows selected Table T_PRODUCT dropped. View PRODUCT dropped.
create table t_product
(
product_id number not null,
product_lang varchar2(3) default sys_context('userenv','lang') not null,
product_name varchar2(20) not null,
constraint un_product unique(product_id, product_lang)
)
;
create or replace view product
as
select product_id, product_lang, product_name
from t_product
where product_lang = sys_context('userenv','lang')
alter session set nls_language=english
insert into product(product_id, product_name) values(1, 'Product 1 name GB')
insert into product(product_id, product_name) values(2, 'Product 2 name GB')
select product_id, product_name from product
alter session set nls_language=french
insert into product(product_id, product_name) values(1, 'Product 1 name F')
insert into product(product_id, product_name) values(2, 'Product 2 name F')
select product_id, product_name from product;
alter session set nls_language=swedish
insert into product(product_id, product_name) values(1, 'Product 1 name S')
insert into product(product_id, product_name) values(2, 'Product 2 name S')
select * from t_product
drop table t_product purge
drop view product
Table T_PRODUCT created.
View PRODUCT created.
Session altered.
1 row inserted.
PRODUCT_ID PRODUCT_NAME
---------- --------------------
1 Product 1 name GB
2 Product 2 name GB
1 Product 1 name F
2 Product 2 name F
1 Product 1 name S
2 Product 2 name S
PRODUCT_ID PRO PRODUCT_NAME
---------- --- --------------------
1 GB Product 1 name GB
2 GB Product 2 name GB
1 F Product 1 name F
2 F Product 2 name F
1 S Product 1 name S
2 S Product 2 name S
6 rows selected
Table T_PRODUCT dropped.
View PRODUCT dropped.
needs a column to tell that information for current row or separate columns.
Yes. Ways are another discussion in other forum.
Horizontal model that got one product row but language dependent data is in separate columns.
create table t_product ( product_id number not null primary key, product_name_en nvarchar2(20) not null, product_name_s nvarchar2(20) not null, product_name_f nvarchar2(20) not null ) ; create or replace view product as with lang as ( select sys_context('userenv','lang') code from dual ) select product_id, lang.code as product_lang, case lang.code when 'GB' then product_name_en when 'S' then product_name_s when 'F' then product_name_f end product_name from t_product, lang ; insert into t_product(product_id, product_name_en, product_name_s, product_name_f) values(1, 'Product 1 name GB','Product 1 name S','Product 1 name F') ; insert into t_product(product_id, product_name_en, product_name_s, product_name_f) values(2, 'Product 2 name GB','Product 2 name S','Product 2 name F') ; alter session set nls_language=english ; select product_id, product_name from product ; alter session set nls_language=swedish ; select product_id, product_name from product ; alter session set nls_language=french ; select product_id, product_name from product ; select * from t_product ; drop table t_product purge ; drop view product ; Table T_PRODUCT created. View PRODUCT created. 1 row inserted. 1 row inserted. Session altered. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name GB 2 Product 2 name GB Session altered. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name S 2 Product 2 name S Session altered. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name F 2 Product 2 name F PRODUCT_ID PRODUCT_NAME_EN PRODUCT_NAME_S PRODUCT_NAME_F ---------- -------------------- -------------------- -------------------- 1 Product 1 name GB Product 1 name S Product 1 name F 2 Product 2 name GB Product 2 name S Product 2 name F Table T_PRODUCT dropped. View PRODUCT dropped.
product_id number not null primary key,
product_name_en nvarchar2(20) not null,
product_name_s nvarchar2(20) not null,
product_name_f nvarchar2(20) not null
with lang as (
select sys_context('userenv','lang') code from dual
select
product_id,
lang.code as product_lang,
case lang.code
when 'GB' then product_name_en
when 'S' then product_name_s
when 'F' then product_name_f
end product_name
from t_product, lang
insert into t_product(product_id, product_name_en, product_name_s, product_name_f) values(1, 'Product 1 name GB','Product 1 name S','Product 1 name F')
insert into t_product(product_id, product_name_en, product_name_s, product_name_f) values(2, 'Product 2 name GB','Product 2 name S','Product 2 name F')
PRODUCT_ID PRODUCT_NAME_EN PRODUCT_NAME_S PRODUCT_NAME_F
---------- -------------------- -------------------- --------------------
1 Product 1 name GB Product 1 name S Product 1 name F
2 Product 2 name GB Product 2 name S Product 2 name F
Code based version demo.
Note! you can of course do that view SQL and filtering language in application code. View and context use is not mandatory.
create table product_names ( product_id number not null primary key, en nvarchar2(20) not null, s nvarchar2(20) not null, f nvarchar2(20) not null ) ; create table products( product_id number not null primary key, product_description varchar2(30), constraint product_names_fk foreign key (product_id) references product_names(product_id) ) ; create or replace view product as select product_id, ( -- lookup select case sys_context('userenv','lang') when 'GB' then en when 'S' then s when 'F' then f end from product_names where product_id = products.product_id ) product_name from products ; insert into product_names(product_id, en, s, f) values(1, 'Product 1 name GB','Product 1 name S','Product 1 name F') ; insert into product_names(product_id, en, s, f) values(2, 'Product 2 name GB','Product 2 name S','Product 2 name F') ; insert into products(product_id, product_description) values (1, 'desc 1') ; insert into products(product_id, product_description) values (2, 'desc 2') ; alter session set nls_language=english ; select product_id, product_name from product ; alter session set nls_language=swedish ; select product_id, product_name from product ; alter session set nls_language=french ; select product_id, product_name from product ; select * from product_names ; select * from products ; drop view product ; drop table products purge ; drop table product_names purge ; Table PRODUCT_NAMES created. Table PRODUCTS created. View PRODUCT created. 1 row inserted. 1 row inserted. 1 row inserted. 1 row inserted. Session altered. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name GB 2 Product 2 name GB Session altered. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name S 2 Product 2 name S Session altered. PRODUCT_ID PRODUCT_NAME ---------- -------------------- 1 Product 1 name F 2 Product 2 name F PRODUCT_ID EN S F ---------- -------------------- -------------------- -------------------- 1 Product 1 name GB Product 1 name S Product 1 name F 2 Product 2 name GB Product 2 name S Product 2 name F PRODUCT_ID PRODUCT_DESCRIPTION ---------- ------------------------------ 1 desc 1 2 desc 2 View PRODUCT dropped. Table PRODUCTS dropped. Table PRODUCT_NAMES dropped.
create table product_names
en nvarchar2(20) not null,
s nvarchar2(20) not null,
f nvarchar2(20) not null
create table products(
product_description varchar2(30),
constraint product_names_fk foreign key (product_id) references product_names(product_id)
-- lookup
case sys_context('userenv','lang')
when 'GB' then en
when 'S' then s
when 'F' then f
end
from product_names where product_id = products.product_id
) product_name
from products
insert into product_names(product_id, en, s, f) values(1, 'Product 1 name GB','Product 1 name S','Product 1 name F')
insert into product_names(product_id, en, s, f) values(2, 'Product 2 name GB','Product 2 name S','Product 2 name F')
insert into products(product_id, product_description) values (1, 'desc 1')
insert into products(product_id, product_description) values (2, 'desc 2')
select * from product_names
select * from products
drop table products purge
drop table product_names purge
Table PRODUCT_NAMES created.
Table PRODUCTS created.
PRODUCT_ID EN S F
PRODUCT_ID PRODUCT_DESCRIPTION
---------- ------------------------------
1 desc 1
2 desc 2
Table PRODUCTS dropped.
Table PRODUCT_NAMES dropped.
Jarrko,
Just a reminder that columns should be defined as (n)varchar2(20 CHAR), for example, to avoid problems with characters taking more than one byte.
Paul Horth kirjoitti: Jarrko, Just a reminder that columns should be defined as (n)varchar2(20 CHAR), for example, to avoid problems with characters taking more than one byte.
Paul Horth kirjoitti:
Good reminder!
Default nls_length_semantics is byte so it is important to remember that. My bug
Thank you So much Experts For your Fantastic explanation.
Is there any way for number datatype ,other then storing as 1,2,3 .Because all country languages have there own numbers.
similarly like varchar2(20)---------> Nvarchar2(20) --------Unicode
Number------->?
One way avoid fixing huge scripts because forgot to declare semantics is to set correct semantics in session before creation of tables. That is good practice also when creating packages or any programs that declare nvarchar or varchar2 in al32utf8 database.
alter session set nls_length_semantics=char ; create table product_names ( product_id number not null primary key, en nvarchar2(20) not null, s nvarchar2(20) not null, f nvarchar2(20) not null ) ; -- check that table is created with correct nls_length_semantics select column_name, decode(char_used,'C','CHAR','B','BYTE') "NLS_LENGTH_SEMANTICS" from user_tab_columns where table_name in ('PRODUCT_NAMES') ; drop table product_names purge ; Session altered. Table PRODUCT_NAMES created. COLUMN_NAME NLS_ ------------------------------ ---- PRODUCT_ID EN CHAR S CHAR F CHAR Table PRODUCT_NAMES dropped.
alter session set nls_length_semantics=char
-- check that table is created with correct nls_length_semantics
select column_name, decode(char_used,'C','CHAR','B','BYTE') "NLS_LENGTH_SEMANTICS" from user_tab_columns where table_name in ('PRODUCT_NAMES')
COLUMN_NAME NLS_
------------------------------ ----
PRODUCT_ID
EN CHAR
S CHAR
F CHAR
A couple of comments to the other answers:
1. No need (or possibility) to declare the length semantics for NVARCHAR2 columns. They always have character length semantics.
2. Oracle advises to use VARCHAR2 columns with the AL32UTF8 database character set in place of NVARCHAR2 columns. NVARCHAR2 columns are not supported by all database features.
Regarding numbers, numbers are abstract mathematical values and do not depend on any spoken language. Therefore, they should be stored in the NUMBER (or BINARY_DOUBLE) data type. The use of non-European digits to display the number, e.g. Arabic digits or Hindi digits or Chinese number characters, is the matter of the presentation layer. It would be nice to have support for non-European digits in the TO_CHAR function, but we do not currently have it. It is a nice enhancement request. For now, for some languages you may just use TO_CHAR to get the European digits and then use TRANSLATE to replace these digits with, for example, Arabic ones. For Chinese, you would need to build a translation function in PL/SQL (or in your client application), because Chinese numbers do not use the same positional system. For example, the number 23 is represented by the characters for 2, character for 10, and character for 3.
Thanks,
Sergiusz
Hi Sergiusz,
i did not know that one although i tried it and failed but did not pay attention enough. Assumptions are mothers of all bugs and misunderstandings.
Thanks for clarifying this and other points
Thanks all Experts.
Hi Experts,
What languages are not yet supported in Unicode?
Unicode defines the encoding of characters.
As far as I know it supports all the characters that languages are written in + quite
a few more characters not in any language.
For practical purposes of business applications and popular computer use, all languages are encoded. For scholar use, many minority (written by a few hundred people) and historical scripts still need to be encoded. See here: Proposed New Scripts. Some less know scripts may also get a few new characters. See here: Proposed New Characters -- Pipeline Table. Note, the not yet encoded scripts and characters are mostly of historical/scientific importance and are encoded to make sure Unicode stays a truly universal character set.