Solved

German Umlaut in WHERE and INSERT Statement

Posted on 2003-12-01
13
2,544 Views
Last Modified: 2007-12-19
Hi all

Got a strange Problem with Oracle 8.1.7

I have a DB-Server and 8.1.7 Client both set to character set US7ACII. The Data in the DB can have german 'umlaut' (ä,ö,ü) in the data, usually VARCHAR2 fields.

It's no Problem just to read data from the DB, means the 'umlaut' s will be displayed correctly if they're allready in the DB. (Conversion was made with native connection, there was no problem with the 'umlaut' s)

If I try to search records with an argument with 'umlaut' there will be no result. If I replace the 'umlaut' with an ?, I get the correct Result (with 'umlaut' s in the result, exaclty where I expect it to be)

If I try to write new recordsets, the 'umlauts' are replaced by their origin character (ö --> o, ä --> a, ü --> u).

If I take the Microsoft ODBC for Oracle, it works fine but instead has problemes with too long queries.

Questions:

- Is there an option to set that the 'umlauts' are accepted also in WHERE and in INSERT? (st. diffrent from installing the db newly, that's no option...)
- Is there a solution with the microsoft ODBC that has no problems with long queries?

thanx for quick answer, many points are waiting for a fast anser! (I will increase points if you provide a fast solution, which I can use to really solve my real world problem!)

cheers

Choli
0
Comment
Question by:Choli
  • 5
  • 4
  • 4
13 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9850942
Choli:

Take a look at this thread, I have answered the same question before in this forum:

http://www.experts-exchange.com/Databases/Oracle/Q_20742241.html
0
 

Author Comment

by:Choli
ID: 9851101
Hi Seazodiac

mmh... it's not exactly the same problem because I don't want a 'accent-insensitve' search but a correct 'accent-sensitive' search...:

- OK, it is one way to search case insensitive, but I'll be able to search exactly....
- There is something more about the problem: ORACLE should also save the 'umlauts' in it's correct form, the values are used e.g. for letters, where it's important to have the correct character.

any other idea or an explanation to the answer why it covers the problem anyway?

Cheers

Choli
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9851154
- OK, it is one way to search case insensitive, but I'll be able to search exactly....

----->That's right, you can use it for exact search too.

- There is something more about the problem: ORACLE should also save the 'umlauts' in it's correct form, the values are used e.g. for letters, where it's important to have the correct character.

---->the fact that you create your Database in US7ACII stops oracle from storing 'umlauts' in its correct form. I am afraid that you did not read the entire answer.

I copies and pasted the QA from the old post:

## 1) will use of NVARCHAR2 allow me store A with an umlaut

Not if the national character set is US7ASCII. You need WE8ISO8859P1 or WE8MSWIN1252. But I would actually recommend using VARCHAR2 with the database character set WE8MSWIN1252 (Windows clients) or WE8ISO8859P1 (Unix clients)
0
 

Author Comment

by:Choli
ID: 9852112

Hmm... still not satisfied...

- OK, it is one way to search case insensitive, but I'll be able to search exactly....

----->That's right, you can use it for exact search too.
****> how? As I understand, the search will just find a and ä with that example. I just wanna have ä....


---->the fact that you create your Database in US7ACII stops oracle from storing 'umlauts' in its correct form. I am afraid that you did not read the entire answer.

We are working that way for years now, and it worked fine till XP and ORACLE ODBC.

Using  Microsoft ODBC for ORACLE it works. Using ODBC native connection, it works. I did a whole migration and all the umlauts are in that DB. I can read them out and show them.


Cheers

Choli

0
 
LVL 47

Expert Comment

by:schwertner
ID: 9856525
Leider US7ASCII kann nicht speichern in dem Datenbank deutsche umlaut  Buchstaben.

US7ASCII can not store umlauts in the database. This is the source of all your troubles. You have to use UTF8 or other character sets. Also on the client machine you have to set that character set in the Registry if you use Oracle Client.
0
 

Author Comment

by:Choli
ID: 9857028
Hi schwertner

haven't seen you for a while ;-) (Ok, I havn't been here for a while...)

Got the same information from ORACLE. The main problem is that the Oracle ODBC-Driver tries to convert the caraters to unicode. The Microsoft driver just passes the data trough. On the Database, Oracle saves unknown characters anyway with 8 bits altough the characterset is 7 bit.

As I see, to be on the secure side, we have to update the DB to 8 bit, which is not an easy task, i'm affraid.

CU

Stefan
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 9857490
I have the instructions how to change the character set of 8i to new one on the fly. Of course you have to backup the database before this to secure the experiment.

Another strategy is to make export, to create a new DB and to do import. Also have good experience to do this.
The best choice for the new character set is UTF8 and derivates. Also migration to 9i is a good idea.
0
 

Author Comment

by:Choli
ID: 9862427
hi schwertner

hmmm.... it's quite a big database withe some million entris. What about the time for running an update? Anyway how to get the instruction? some extra points?   ;-)  

I'm not sure if exporting the data and re-import them is suitable, again the question is about the time... and what is the thing with 9i? Is the problem solved there?

thanx 4 anser

choli
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9862740
Upgrade will cause some downtime.
but EXP-IMP will NOT. you can do this while the database is up and running.

if configured properly, EXP/IMP goes pretty quickly and  smoothly. of course, the best thing is that you don't have to make a copy or backup of working database, while you are building new one.

0
 
LVL 47

Expert Comment

by:schwertner
ID: 9864776
You do not need to move to 9i only for the umlauts. If you set in the enw 8i installation character set to UTF8 you will have no troubles. Migration to 9i is because it is new version and better of course.

The instruction to change the character set will be sent to you if you send me an email jschwert99@hotmail.com. It is risky, so make an backup ane export before applying.

Export/import is a good solution. there are some small traps, but I just prepare a migration strategy for Germany using import/export and am well informed how to avoid the troubles.
0
 

Author Comment

by:Choli
ID: 9880538
As I'm not the system engineer for that system, I can't exactly control or push what version they have to use. But of course I'm urging them to convert to 8 Bit, probaly really UTF8, but they have to check that out first.

I'm not sure about the time slot we have for an export - import but I think it's the only way.

By the way, I found the problem with the Microsoft driver. It is not the number of columns but they don't support types as BLOB or CLOB. So we had to change the field-type to a large VARCHAR2. But as mentioned before it's too risky to work on an unsupported or even corupt dataset.

schwertner, what about closing and splitting this issue and reopen another one for the migration strategy?
0
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 9882145
Good luck! Do not worry about points, I do not see way to use them!
Dass ist nur fuer Spass!
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9882400
Choli:

In fact, you may not have to migrate your database.

First, let's check out what CHARSET and NCHARSET your database is really built upon.

at the SQL PROMPT,

SQL>>select name , value$ from sys.props$  where name like '%NLS_NCHAR%';

this will give your national characterset of your database.
what you may have is already UTF8, because mostly database is built upon that at the minimum.

suppose if you are already in UTF8, then all you need to do is to convert some columns in the tables to NCHAR or NVARCHAR2 type from CHAR or VARCHAR2,
then you are good to go...

Hope this helps
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now