hi,
sorry, my question wud seem very silly but none of the books nor manual talk abt these things with an example. incase if its repeated question my apologies for that. even if u know some link i wud be very happy rather than, u wasting ur time in explaining.
even if someone wud send me the link it wud be fine with me :)

is it something like this nvarchar2 refers to national character set and varchar2 refers to database charecter set. whatever character set i choose in national character set it wud refer to nvarchar2. suppose if i've a national character different from database character then i wud use nvarchar2.
(a) like suppose my data would contain some in chinese and some in english but the source code (sql / plsql statements) wud be in english. to store chinese i'll use nvarchar and for english i wud use varchar2 but the database char set wud be 7bit ascii

(b) suppose i've source code in chinese and data also in chinese then multibyte character set must be choosen for both database character set and national character set. which one we'll be using nvarchar or varchar2? i think we'll be using nvarchar2 throughtout our database. inshort varchar2 datatype is not used at all ? am i correct ?

(c) i didnt understand the concept behind why we cant use a fixed-width multibyte character set as database character set ?

(d) also i cud not understand the sorting as per major and minor in monolingal sorting ?

expecting to get clear answer.

Bad luck YenYang ! I was sure that this would happen. The "chatting language" is the one thing that makes Tom furious. He had already expressed his discomfort on this many times before. Tom, here is a known Achilles Heel to your otherwise prodigious patience :-)

Hi Tom,
Well i read about the varchar2 and char, and then the nchar and nvarchar2 data types. To be frank i am a bit confused from the documentation as it does not explain it in depth.
Could you please explain these two datatypes and how they differ from the conventional ones? And also, under what circumstances should they be used?
Thanks as always

Hi Tom,

i am working on ORACLE 10g R2 , i need to create a db table which supports muliple languages (Internationalization).
As mentioned above in this article

A) when i need to go for Globalization support my table should support/accept these data ( ENGLISH , JAPANESE,FRENCH)etc
should i have 2 columns 1) to store english data & 2) to store data other than english if my DB (WESTERN) & National character set(AL32UFT8) are different

B)I have a column "DESCRIPTION" i entered the value
"WHAT IS YOUR NAME" in this column in english, but if any other language person needs to view this should i store the equavelent data in other languages or oracle will convert this
for example: if i need to see the same data in french will oracle convert into "qui est nome ? " ro i need to design the table to store the equavelent data

i read the latest "Going Global "article
:</code> https://asktom.oracle.com/Misc/oramag/my-first-day-with-the-new-release.html <code>
Oracle9i Database introduced character semantics, which changed the way multibyte characters were treated. Instead of doubling or tripling column or variable precision, setting NLS_LENGTH_SEMANTICS = CHAR causes Oracle Database to treat storage of the string 'Today' the same as the Japanese string '&#12371;&#12435;&#12395;&#12385;&#12399;'.

Hi Tom,

Thanks for your reply and the the documentation link.
As mentioned by you
"You probably want a single characterset capable of holding any of your languages, not a character set and National characterset. "

What is the probable single characterset (oracle 10g R2) i should use to store multilanguage support . (english,spanish,french,japanese)
just a suggestion from you !

I am reading the oracle10g globalization support document currently.

thanks
thirumaran

Hi Tom,
We are in the process of developing application to support CJK and Western European languages.

We use AL32UTF8 as character set and AL16UTF16 for National character set.

We are developing application and using UTF-16 Unicode( OCI wide API) because of faster string processing capability.

1.Is it good approach to change every CHAR and VARCHAR2 columns in the database to NCHAR and NVARCHAR2 to avoid character set conversation from UTF 8 to UTF16.

2. Is conversation from UTF-8 to UTF-16 considerable overhead?

3.We have more Western European characters (85%) and few Asian characters; I am guessing that if we convert every VARCHAR to NVARCHAR2 we may almost double the database size and requires to double SGA size for same performance before conversion. Could you advise if this is reasonable guess?
4.Our application team using UTF-16 for faster string processing (over UTF8 variable width) but database uses UTF-8 not UTF-16 for CHAR type, Is is good approch to develop application to use UTF-8 instead of UTF-16 ?
Thank you,
V

Hi Tom,
We are in the process of developing application to support CJK and Western
European languages.

We use AL32UTF8 as character set and AL16UTF16 for National character set.

We are developing application and using UTF-16 Unicode( OCI wide API) because of
faster string processing capability.

1.Is it good approach to change every CHAR and VARCHAR2 columns in the database
to NCHAR and NVARCHAR2 to avoid character set conversation from UTF 8 to UTF16.

2. Is conversation from UTF-8 to UTF-16 considerable overhead?

3.We have more Western European characters (85%) and few Asian characters; I am
guessing that if we convert every VARCHAR to NVARCHAR2 we may almost double the
database size and requires to double SGA size for same performance before
conversion. Could you advise if this is reasonable guess?
4.Our application team using UTF-16 for faster string processing (over UTF8
variable width) but database uses UTF-8 not UTF-16 for CHAR type, Is is good
approch to develop application to use UTF-8 instead of UTF-16 ?
Thank you,
V

Hi Tom,
Could you please reply to my questions above.
Thanks a lot.
V

I feel that there are some good question pending on this subject, that my self have been wondering around, may be those once have creep in to this thread (I believe those are very worth considering Dear Mr. Tom) but if any of you know where to find the information on more details related to performance implications on using unicode db and nvarchar approach, I would appreciate it.

I personally believe that NLS_NCHAR is for previous era, prior to unicode approach, so if building new applications should use unicode, if you have something old/legacy :) stick to NLS_NCHAR's.

Tom
As always your input is most valuble in Oracle users community. COuld you please take some time to answer my question above ?
Thank you,
V

Hi Tom,
After having read this discussion and a several Metalink TARs without a straight answer from Oracle I would like to get your opinion on this:
We are implementing Oracle Database in several Western and Eastern European countries and are (depending on the necessity of mixed clients) either using a Unicode (UTF8) or one of the appropriate ISO (WE8ISO8859P15, EE8ISO8859P2, etc.) characters sets on the server.
In a typical environment, we now use clients using the same character set as the database or if the database uses UTF8 a subset of the database character set.
So far so good, but what if someone running against a non Unicode character set changes the character set to no longer be a subset. If for example client and serer both use WE8ISO8859P15 Oracle simply stores and retrieves the clients characters without any translation but what if a client with character set EE8ISO8859P2 uses the same server. It is my understanding, that Oracle in this case Oracle simply stores the characters tat are not part of the server character set in "some free" location within the server character set and by doing so mixes characters on the server and can no longer the correctly retrieved by other clients.
I do understand that there isn't a lot Oracle can do in such a configuration but why does it not simply reject this configuration of client and server or at least warn about this kid of misconfiguration between client and server character set? For us this is the number one short come in Oracle's I18N support and was never able to understand it.

The problem you describe is one of the (many) reasons I think it's irresponsible in this day and age to build a system in anything but Unicode.

Why do you want to make your life painful (and expensive!) by supporting systems in multiple character sets? KISS - Keep It Simple Stupid!

That said, it would be nice if Oracle has some validation routines that could be used to determine which if any characters in a Unicode string are not valid characters within a given national language...

what does
"might be on the fritz" mean ?
I'm not a native English speaker and I didn't hear that before.
I know "Fritz" only as a German Christian name and also
"Deep Fritz" as the world's best chess program.

http://m-w.com/dictionary/fritz

Main Entry: fritz
Pronunciation: 'frits
Function: noun
Etymology: origin unknown
: a state of disorder or disrepair -- used in the phrase on the fritz

Hi Tom,

I agree with you that there is nothing the database can do if the client character set does not match the server character set but cannot follow why the database should not "warn" if we are going to loose data.

Clearly the warning must be "shown" by the application but Oracle could inform of a mismatch. The Oracle client could recognize the mismatch (that would cause to loose characters) and for example return a status (not an error) like the ORA-24345 or other ones that notify warnings.
The minimum would be an OCI level function that can be used to ask if the character sets would match.

Cheers

Hi, firstly thanks for you attention.

Please I need your advice. We have to create a structure to keep data translation, for instance, in my product table we have to keep the english product description, and french product description. The problem is, some language terms are differents according to a region, for example American says subway, but English people says tube. We are planning to create one table for each language, and one more for recording exceptions (term differents) this is our select:

SELECT t.translation, field_id
FROM tint_data_content_en t
WHERE t.table_id = 'PRODUCT_TABLE'
AND t.field_id IN ('DESCRIPTION','SHORT_NAME') /*Field to Get translation*/
AND t.table_index = 1 /*This is Record PK in Prodcut_Table*/
AND NOT EXISTS ( SELECT 1 FROM tint_data_content_en_uk e
WHERE t.table_id = e.table_id
AND t.field_id = e.field_id
AND t.table_index = e.table_index)
UNION
SELECT translation,field_id FROM tint_data_content_en_uk x
WHERE x.table_id = 'PRODUCT_TABLE'
AND x.field_id IN ('DESCRIPTION','SHORT_NAME')
AND x.table_index = 1

I don't how can optimize this SQL SELECT. If you see we are using the same table in some way.

By The way, In the beggining we were thinking to create multi-language fields as XMLTYPE or CLOB, that is, inserting int the same field language-tags for each language ? something like:

<en>subway</en>
<en_uk>tube</en_uk>
<es>metro</es>

My position is this solution would be more efficient than the current one. What do you think?

Thanks in Advanced, And I'm sorry beacuase I'm not native english speaker.


-- Create table
create table TINT_DATA_CONTENT_EN
(
TABLE_ID VARCHAR2(32) not null,
FIELD_ID VARCHAR2(128) not null,
TABLE_INDEX VARCHAR2(128) not null,
TRANSLATION VARCHAR2(4000)
)
/

alter table TINT_DATA_CONTENT_EN
add constraint TINT_DATA_CONTENT_EN_PK primary key (TABLE_ID,FIELD_ID,TABLE_INDEX)
/

create table TINT_DATA_CONTENT_EN_UK
(
TABLE_ID VARCHAR2(32) not null,
FIELD_ID VARCHAR2(128) not null,
TABLE_INDEX VARCHAR2(128) not null,
TRANSLATION VARCHAR2(4000)
)
/

alter table TINT_DATA_CONTENT_EN_UK
add constraint TINT_DATA_CONTENT_EN_PK primary key (TABLE_ID,FIELD_ID,TABLE_INDEX)
/

Hi Tom,
in my database, I have a table which some of its attributes are in multi-byte character. I have done some researchs and these are the results found:
1- oracle provide NCAHR and NVARCHAR2 types for attributes to be persisted in multibyte characters,
2- we must set NLS_LANG environment variable used to define language, territory and character set properties: export NLS_LANG=Japanese_Japan.JA16SJIS for japanese charcter set for example (for oracle we have also ORACLE_HOME and ORACLE_SID).

To test insertion/retrieving of multibyte characters, I have:
1- create a new table called 'TEST' which some columns types are NVARCHAR2
2- export NLS_LANG=Japanese_Japan.JA16SJI
3- restart Oracle
4- insert into TEST a new row which some attributes are in japanese character set via a java porogram
5- if we select attributes in multibyte character from TEST table we retrieve exclamation points set: ¿¿¿¿¿¿
I think that we need configurations else. what do you think ?

--Best Regards,
Bilel

Hi Tom,

I have read about utl_i18n somewhere. but this is not mentioned in Globalization guide. what is this package.
How this can help in multilingual database applications.

I am still going through globalization guide - the answer I am seraching for is -

I have a table where I have a column whihc stores data in English as well as Chineese.

when I query on that table using

select * from t where col1 = 'ABCD' it works ok.

when I query

select * from t where col1 = 'chineeze value' then it does not return any rows..

WHY ?

I am using Oracle SQL developer.
If you could just point to some document, that would be really a help.

Thanks

Other threads discuss how to deal with these, e.g.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10250420313043#27317016562512

Dear Tom,

The second part of the original question, marked as (2), has still one interesting nuance which I am not sure was fully addressed.

When the post said "So does this mean that now we dont need to use Nchar or nvarchar2 datatype", I believe the implication is still an interesting one.

The Oracle default for VARCHAR2(20) is VARCHAR2(20 BYTE). However, by having the VARCHAR2 datatype also support VARCHAR2(20 CHAR) -- meaning, accommodate literally 20 characters, even if, due to their character set, that will exceed 20 physical bytes of storage -- it makes one think that VARCHAR2(20 CHAR) is already somehow supporting international e.g. Chinese characters.

If so, I am left with the same question -- why is NVARCHAR2() additionally needed?

Conversely, if -- unlike the NVARCHAR2() datatype -- the VARCHAR2() does not support international or special characters, why then would there be variability between the length that a VARCHAR2(20 BYTE) vs VARCHAR2(20 CHAR) can accommodate?

Is this simply due to the existence of characters that are already multi-byte i.e. will not fit in VARCHAR2(1 BYTE), but not yet truly international i.e. not requiring NVARCHAR2?

Perhaps my lexicon can benefit from a more precise way to refer to these two seemingly different classes of characters.

Thanks!
Eugene.