tried it with rich text:
!> 2@5<Q= F0@O >@>E0.
Main Topics
Browse All Topics
Loading Advertisement...
Question
saving russian characters in an oracle database
is it possible to store russian characters in a oracle 10g database with characterset NLS_CHARACTERSET WE8MSWIN1252
and if not, in what characterset should the database be installed to allow Russian and Malaysian ?
i tried copying russian characters '6825B5 75;>, 75<;O, 8 865 8 :0:> ;N48' in the database, but no success
all i get is question marks. ?????????
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Subscribe now for full access to Experts Exchange and get
Instant Access to this Solution
- Plus...
- 30 Day FREE access, no risk, no obligation
- Collaborate with the world's top tech experts
- Unlimited access to our exclusive solution database
- Never be left without tech help again
Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.
- "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
- "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
- "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.
See what Experts Exchange can do for you.
Got a question?
We've got the answer.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
Need individual assistance?
Our experts are ready to help.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Want to learn from the best?
Read articles from industry experts.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Working on a long term project?
Store your work and research.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
What Makes Experts Exchange Unique?
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Trusted by the world's most respected brands.
Faithfully serving IT professionals since 1996.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Free Tech Articles
-
WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
-
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
-
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
-
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
-
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
-
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...
Cloud Class Webinars
- Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
- Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
- IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
- Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
- Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
- How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...
Join the Community
Give a Little. Get a Lot.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Answers
The database should be created either with the character set AL32UTF8 or with CL8MSWIN1251.
Only in this case tha DB can store Russina letters.
The client side will help only to display correctly the letters,
but the NLS_LANG cannot ensure that these letters can be stored.
If you have a running DB with Data inside, use Export/Import (clasic Export/Import)
to create a new DB and to import the data there.
Be aware that due bug Data Pump possibly will not help you to transform
the character set, but give a try.
Though Tomac is correct, it is not advised to make use of N* types / NATIONAL CHARACTER SET in this day and age. Rarely is it useful. Oracle UNICODE/UTF support is such that the best practice for international databases is to use a UNICODE primary character set and to set the correction translation in the client, if needed.
Subject: NLS considerations in Import/Export - Frequently Asked Questions
Doc ID: 227332.1
7. How does NLS affect datapump (expdp/impdp)?
..............
Note:
-----
There is a chance to have data corruption when going from a 8 bit
characterset to UTF8 or a other multibyte characterset on ALL 10g versions
(including 10.1.0.5 and 10.2.0.3) and 11.1.0.6.
Impdp may provoke data corruption unless you applied Patch 5874989.
This is caused by the impdp Bug 5874989.
The "old" exp/imp work fine. This problem is fixed in the upcoming
10.2.0.4 and 11.1.0.7 patchset.
All existing patches for this bug are found here:
http://updates.oracle.com/
For 10.1.0.5, 10.2.0.2, 10.2.0.3 and 11.1.0.6 you can ask a backport
for Bug 5874989 if there is no patch yet for your version/platform.
For windows the fix is included in
10.1.0.5.0 Patch 20 (10.1.0.5.20P) or later ( Note 276548.1 )
10.2.0.3.0 Patch 11 (10.2.0.3.11P) or later ( Note 342443.1 )
The patch is technically only needed on the impdp side, but if you use
expdp/impdp between different character sets we suggest to patch all your
systems.
The short answer to your question is Yes. If you run the Character Map program on Windows then you can see the available characters. I don't think it's a full alphabet, but there are some Cryllic lettters in there.
WARNING: The NLS_LANG parameter must always match the setting of the client machine. (e.g on Windows WE8MSWIN1252 or CL8MSWIN1251, etc.)
DO NOT JUST MATCH THE NLS_LANG ON THE CLIENT WITH THAT ON THE SERVER!!!!!
If you do this, it disables Oracle character mapping and results in a mess of characters in the database. Here is a quick example of what can happen:
Database: Unix Server running and Oracle DB with US7ASCII character set
Client: WIndows PC running Oracle client with US7ASCII character set (even though the PC natively runs WE8MSWIN1252)
If a user inserts a British Pound sign '£' into the database, (Dec: 163, Hex: A3, Bin: 10100011). You will notice that this is > 7-bits (i.e. > Dec: 128), and we are storing it into a 7-bit database... BUT because the NLS_LANG setting matches the database encoding then Oracle will blindly store it.
In reality your database is storing characters in WE8MSWIN1252 format, but it doesn't know it... and if you EVER try to access the database using a different character set (CL8MSWIN1251, UTF8, etc.) then you are going to get character set mis-matches.
Here is a good Oracle URL on the subject:
http://www.oracle.
T
Regards.
Greg.
Great post Greg, my only issue is that I disagree that Oracle will "blindly" store it. It is going to be translated according to the setting on the server and the client. It is not as if Oracle will compare client and server and just say "OK I don't need to translate." Storing an 8 bit character in a 7 bit database will result in data loss on the server. Possibly that is what you meant, but not what I read.
The answer of your question is no!
WE8MSWIN1252 stands for Western Europe alphabets.
Cyrilli is not Wesrtern Europe languge and is not represented in this
character set.
CL8MSWIN1251 (CL stands for Cyrillic) and AL32UTF8 can store Cyrillic letters.
I work with databases that stores Cyrillic, English and German letters and know this for sure.
@ mrjoltcola: I can understand and can not appreciate your exclamations from a wrong and misleading posting!
Hi mrjoltcola,
Actually Oracle will blindly store characters so long as the NLS_LANG on the client and the NLS_CHARACTERSET of the database match. Here is an excerpt from note 158577.1
* If the NLS_LANG is the same as the database characterset then Oracle
(for performance reasons) will do NO validation of the characterset.
See "3.2 A detailed example of a *wrong* nls setup to understand what's going on"
If they match, then translation is turned off for performance reasons... so Oracle will store whatever is sent to it, even if that means storing an 8-bit character into a 7-bit database. Nasty little problem that will only show up when you start using multiple client architectures, or attempt to upgrade your database from US7ASCII to UTF8, etc.
Greg.
@schwertner: I have misread the thread, sorry. I focused on the portion of Greg's post (http://#24538324) and misread the other, regarding the WE vs CL character set. Sorry, I failed to notice the detail at the very top, by the time I got to the bottom, as I was distracted by the other part I disagreed with! Perhaps I should retract my congratulations? :(
Apologies!
schwertner: You are correct. My PC was showing UTF8 characters in the Character Map program. When I switch to Western European, all of the Cryllic are missing. :-(
So to update my previous comment, you cannot "correctly" store Cryllic characters in a WE8MSWIN1252 database. There are some mis-configurations that will appear to work (e.g. NLS_LANG=WE8MSWIN1252, even though the client is actually CL8MSWIN1251)... but do NOT go there, you will endure a lot of pain sometime in the future...
The simple answer these days is to ALWAYS run your database in UTF8 character set, and you'll never have a problem. You never know when your Russian company will suddenly want to start dealing with Chinese, or Korean customers... and if the database is already in UTF8 then you don't have any work to do.
If you need to see what characters exist in a particular character set, then use the Oracle Tool called "Locale Builder", but make sure you PC has a Unicode font loaded, otherwise you'll just see a lot of blank boxes.
Greg.
Greg, mrjoltcola :
No problem in our friedship! But the Asker has problem and has to solve it.
I am working more then 12 years in multilingual Oracle applications. Even in United States our Forms and Reports had to 'speak' 3 languages because the American Insurance had offices not only in U.S. but also in Poland and Brasil. Here we are using at least 3 Languages in our products. It is a nightmare to create Oracle Reports that work with Cyrillic letters. I have a study that shows the best combination of OS and Reports to achieve the result! Nightmare!
@schwertner: I strongly agree with your sentiment regarding misinformation. I have similar concerns about Backup and Recovery threads recently where DBAs have directed posters to take rash direction, such as incomplete recovery, prior to understanding all the details! See my profile for the link.
The humor here is the disagreements are about 2 different issues. Whether Oracle will do translation or not, and whether it is possible, under a correct or incorrect configuration to store 8 bit characters in a 7 bit database.
I stand corrected on all counts, due to both my misconception about Metalink 158577.1, and due to my misreading of Greg's original note.
I also notice misinformation from other Experts in this zone on a weekly basis, but I DO try to be a bit more polite about correcting it. Thank you.
@Geert_Gruwez:
IMPORTANT: To clear up all the confusion please refer to schwertner's post: http://#24538835 as we have sufficiently cluttered this thread.
@ Geert_Gruwez
Furthermore I have two good and two bad news for you.
1 bad news: If you make the best decission to use AL32UTF8 character set the Cyrillic (Russian) letters wil ocupy 2 bytes in the DB. So if you declare a column
NAME VARCHAR2(35);
you will be able to store there there only 17 Russian letters (or 35 English!).
So you have to change the declarations to:
NAME VARCHAR2(35 char);
NAME VARCHAR2(70);
2 bad news: In the future you possibly will be asked to create some kind of Reports. It is not easy task to force the tool to represent Cyrillic letters. You have to investigate the Reports tool in advance.
Now the good news:
1 good news: Using classic Export/Import and precreating a DB with AL32UTF8 and setting NLS_LANG on both sites to correct value yu'll be able to transfer the DB. In some cases Data Pump will also help you - I do not know how severe the bug is.
2 good news: I know how to use Oracle Reports with Cyrillic letters (Install standalone Forms/Reports server under Windows 2000 and localize the machine to GUS - the current name of Russia).
You see, obviously president Reagan was correct ... but I stop here!
hey guys, great responses.
i just heard russian is just the start.
I'll probably be asking a lot of questions in the future about unicode.
The languages will probably be Russian, Malaysian and existing (American, ) Dutch, English, French, German
The idea would be to create 1 table for all translations
An id for the language, the table, the record and off course the translated text
Reporting isn't a problem. I've written my own reporting tool.
Storing the data in the database is currently the only problem.
@Geert_Gruwez:
If you're going to handle a flood of different languages, then UTF8 should be your only choice. Choosing any other character set will eventually leave your database lacking, and force you to convert it... which isn't fun.
As mrjoltcola points out in answer #24536361 the use of NCHAR/NVARCHAR is not really the recommended way these days... You're going to have to bite the bullet and switch your entire database to UTF8.
Metalink has a lot of good articles on how to do the switch, and the link I previously posted should get you started.
Greg.
AL32UTF8 only.
But this is the smallest problem.
The real problems comes with the Forms and Reports.
From one side they have to be unified, from the other side - multilingual.
Ask me what means to make 3-lingual Oracle Forms and Oracle Reports
in the States - English, Polish and Portugees for the local branches of the Insurance.
Based on the existing languages the most important thing (for clasic Export and Import) is to set NLS_LANG
parameter on both instances and to check if the non English letters are transfered correctly.
EE will have to give a new status to people answering questions.
1: People with no experience in a certain domain and no experience in asking questions
These people have no idea what the actual good answer is ...
until they get experience with that subject
and no idea of how this site works for grading
2: People with no experience in a certain domain and some experience in asking questions
These people have no idea what the actual good answer is ...
until they get experience with that subject
and some idea of how this site works for grading
We can go on like this with an ever higher experience for the domain
and an ever higher experience in this EE website.
Besides that, once an asker gets his answer or something near enough,
to my experience, most just click here and there to accept and don't spend the time to reading everything in minute detail.
My appologies for doing the same.
@schwertner
May i point out that you may have added some more experience for the oracle part.
Everybody does make a mistake (We are after all, only human)
I admit that when you take all small mistakes like this for receiving grades for lots of questions you have lots of annoyance.
I now understand some things that happened with ciuly. http://www.ciuly.com/ee/bl
I also find it amazing that intelligent people degrade themselves into assuming that everybody has the same level of intelligence as they do.
Or are these titles "PhD in Comp. Sc., Assoc. Professor in Comp. Science" just something bought with a box of Kellogs Corn Flakes ?
Maybe you can use his scripts to automate your system to releive you of further annoyance of users like me who didn't spend their time going over all the texts again.
I asked a question and it did get answered.
In my mind i was actually allready planning ahead and thinking of what i had to do to enable Russian (and Malaysian, and in that state i accepted the answers, which were the solutions to that...
So if we sum this up, we are annoying each other.
I annoyed schwertner, and he annoyed me by not clarifying.
Does this give you guys a better feeling and understanding of a less experienced asker in the oracle domain and asking questions ?
Now i will look into accepting a solution keeping in mind the original question.
Since EE states i need to give feedback ...
ID:24534916 Author:Nysso
Try to set on client NLS_LANG=. CL8MSWIN1251
didn't work.
ID:24534953 Author:schwertner
The database should be
in English "should" means (this is an advise, not a necessity)
I'll have to set up a database to test this.
I'll take yer word for it.
ID:24535318 Author:Tomac_Yao
nvarchar is a possible solution, second part is contradicted later
ID:24536361 Author:mrjoltcola
setting it in the client ...
hmm, our application will be able to switch between languages at runtime, do you switch the NLS_LANG too then ?
ID:24536461 Author:schwertner
this is a very good pointer to keep in mind.
ID:24536700 Author:schwertner
advice on how to solve
ID:24538324 Author:gregclough
contains contradictions
ID:24538411 Author:mrjoltcola
allready pointing out this wrong info
ID:24538835 Author:schwertner
this not appreciating was i guess meant for gregclough and not for mrjoltcola ???
further posts are a a game of tag ... about which of these posts were correct.
and i got lost in who was stating what
in the end everybody seemed to agree for AL32UTF8
so i accepted those last pieces.
the problem with people is i tend to put them on a pedestal. the more intelligent the higher the pedestal.
schwertner, ... you just fell off with your blacklist comment, and it was a looooong way down !
what would you do to somebody who gives good advice one moment, and the next tells you to "get lost" ?
so i will accept those answers again, and some more, evenly dividing the points for the time spent.
ah ... evenly ... because i don't know who spent more time and who didn't, i can't make it any fairer than that.
@ Geert_Gruwez:
It is your right to distribute the "points" (they have no value!).
It is my right to make the decission to waste further my time answering questions (for nothing!).
I had in mind a bad scenario:
1. You accept as true that your current character set can store Cyrillic letters
2. You report this to your boss
The results could be not very pleasant either for the business of the company and (possibly!) for the opinion
your bosses have about you.
I have yearslong experience to run Oracle in multilingual environment. And I tried to warn you.
I will tell you a story.
In March 2004 I flew to Germany and made migration from Oracle 8i to Oracle 9i using Export - Import way.
But I was not experienced DBA and did the mistake not to set NLS_LANG on both instances.
I couple of hours after the returning from Germany my local boss went in my office and said me:
"The German Umlauts are not transfered! You are .....!"
I assure you that this was like a strong earthquake ... for my nervs, for my reputation, for my mood.
..
I was lucky! The German office has not destroyed the Original Oracle 8i installation.
They set NLS_LANG, do next Export-Import and in a couple of hours the problem
was solved.
But imagine what could happen, if they have destroyed the 8i installation!
I tried only to save you such adventures!
Sorry ...
yes, I am annoy
I do not ask points.
You have the right to distrubute the points and you have done your
decission. Let this decission will be the final decission.
Do not redistribute the points - they have no value at all.
We are not paid, not honorred, ....
Further discussion are meaningsles.
Thank you for your time.
Good bye!
I took your pointer about not spending enough time in accepting the right answer.
The reporting tool is built by ourselves using following:
Delphi 2009 : www.embarcadero.com
DevExpress : www.devexpress.com (quantumgrid)
Odac : www.devart.com
And for your assurance ... we keep backups 6 months on the local disks for a conversion.
So don't worry !!!
I feel like I kicked all of this off, and I can only apologize that my eagerness to answer has resulted in such pain. I got caught out by the fact that "Character Map" on windoze used to display WE8ISO8859P1 by default, but now uses UTF8 and I didn't double-check. Doh!
Consider me duly chastised, and I'll always rely on Locale Builder in future.
Greg, thanks. You also added some information regarding translation that I did not know, so don't let it discourage your participation. We can all learn to do our homework before chiming in. The important thing is that we did not let bad advice go uncorrected, and so the OP got the correct answer in the end. As schwertner pointed out, this is critical, because wrong choices sometimes result in someone getting fired.
Welcome to EE. :)
Everything is OK!
The problem is that we are divided in proportion 3:1.
You are 3 good guys that have not deep experience in
working with more 'exotic' languages like Russian
and have no idea how Oracle and clients works with
such alphabets.
I am 1 (one) bad guy that speaks exelently two languages
with Cyrillic alphabet (Bulgarian and Russian and have experienced
adventures with applications based on such Oracle installation.
So I loosed my mood and explain in details almost all my notes
collected in regard of this environment.
This is my mistake.
The better way was to go away, do not publish my notes
from metalink and give you the possibilty to learn the live
in the Russian trenches on your own.
This is a joke, of course! We have to help each other
because the life is a hard thing.
So I appologies for my sharp words and wish you guys
all the best!
Speaking for myself, I do not view you as one of the bad guys. :) You were the voice of sanity in this thread and were the reason that the correct answer was emphasized. The forum is more about correct answers than anything. If we are afraid to disagree with incorrect information, then we cannot provide the best solution.
It's not only Russian trenches, believe me.
I earned my black belt in Japan, so i have some experience with hiragana and katana.
I admit not Japanese database related, that will still come.
But the blows i got dealt by this Japanese Shihan i can still feel in my ribs.
So there is a lot more trenches than just database ones.
Besides that, living in Ypres, Belgium is a constant reminder of a warzone.
We dig up bombs every week still. and that after 90 years !
The trenches of the so called "National Language Support" in Oracle are dirthy place for programmers.
The programmers working only with English alphabet possibly are happy (in Oracle matter),
programmers working with West European alphabets are not so happy (again in Oracle matter),
but the programmers working outside these two zones experience big difficulties.
My mistake is that I am trying to help you instead laughing from the bushes.
I am a naive old programmer .... oldfashioned possibly!
3 Ways to Join
Business Accounts
- Perfect for organizations
- Multiple users
- Save over 36%
- Create a Business Account
Answer for Membership
- Earn free access
- Showcase your knowledge
- No credit card required
- Sign Up to Answer
Loading Advertisement...
by: Geert_GruwezPosted on 2009-06-03 at 03:06:38ID: 24534766
ugh, guess this website doesn't handle russian chars very well either ...
i got some texts of the wiki for russian:
http://en.wikipedia.org/wi