Umlauts in oracle7

From: Michael Rieder <zhrmc_at_zh014.ubs.ubs.ch>
Date: 26 Oct 93 07:32:52 GMT
Message-ID: <1993Oct26.073252.7128_at_zh014.ubs.ubs.ch>

Im currently converting a database from Ingres to Oracle7. So far I've found my ways to transfer the table-structure etc. But now I'm hanging on one of those never ending problems: There are some umlauts in the original data that have to be preserved. I've tried quite a few things (they are listed bellow) but it looks as if oralce7 would simply ignore my will.

>Question:

        Had anybody else similar problems? Could it be solved? And if so, how?

        I would greatly apreciate any responses.

        Thanks

        Michael

>Envrionment:

	ORACLE7 Server Release 7.0.12.2.0,
	Charactertable used with Oracle7: WE8ISO8859P1,
	SunOS Release 4.1.3



>Problem:

	The not 7-bit-world has to face characters like 'dv|'
	(in case your system scrambles them, 'a with dots, o with dots, u with dots').

	At 'INSERT INTO table VALUES ( ...)' and with
	SQL*Loader-Runs these entered or in loader scripts included
	characters don't seem to be reproduced correctly. Oracle7 seems
	to convert them to different characters.

	SQL*Loader has an option for character-conversion, but using it
	didn't help at all, the result stayed the same (I've used it with
	several available character tables.)

	Even the 'SELECT' statements with 'CONVERT' didn't give the expected
	(or hopped) result. 



>Reproducability:

	Create a dummy-table 'mike2':

	CREATE TABLE mike2 (tchar char(3));

	Execute a 'INSERT' statement:

	INSERT INTO mike2 VALUES ('dv|');
				   ^^^
				   |||
				here you should use those fancy characters
				like 'd' ('a with dots') etc.

	Review the data in the table with 'SELECT':

	SELECT tchar FROM mike2;

	Tuple displayed by Oracle7:	d|V

	A few examples of the 'CONVERT' function:

select convert('d|v', 'US7ASCII') from mike2;
select convert('dv|', 'WE8DEC') from mike2;
select convert('dv|', 'WE8HP') from mike2;
select convert('dv|', 'F7DEC') from mike2;
select convert('dv|', 'WE8EBCDIC500') from mike2;
select convert('dv|', 'WE8PC850') from mike2;

select convert('dv|', 'WE8ISO8859P1') from mike2; select convert('dv|', 'JA16EUC') from mike2;

select convert('d|v', 'US7ASCII', 'US7ASCII') from mike2;
select convert('dv|', 'WE8DEC', 'US7ASCII') from mike2;
select convert('dv|', 'WE8HP', 'US7ASCII') from mike2;
select convert('dv|', 'F7DEC', 'US7ASCII') from mike2;
select convert('dv|', 'WE8EBCDIC500', 'US7ASCII') from mike2;
select convert('dv|', 'WE8PC850', 'US7ASCII') from mike2;
select convert('dv|', 'WE8ISO8859P1', 'US7ASCII') from mike2;
select convert('dv|', 'JA16EUC', 'US7ASCII') from mike2;

select convert('d|v', 'US7ASCII', 'WE8DEC') from mike2;
select convert('dv|', 'WE8DEC', 'WE8DEC') from mike2;
select convert('dv|', 'WE8HP', 'WE8DEC') from mike2;
select convert('dv|', 'F7DEC', 'WE8DEC') from mike2;
select convert('dv|', 'WE8EBCDIC500', 'WE8DEC') from mike2;
select convert('dv|', 'WE8PC850', 'WE8DEC') from mike2;

select convert('dv|', 'WE8ISO8859P1', 'WE8DEC') from mike2; select convert('dv|', 'JA16EUC', 'WE8DEC') from mike2;

select convert('d|v', 'US7ASCII', 'WE8HP') from mike2;
select convert('dv|', 'WE8DEC', 'WE8HP') from mike2;
select convert('dv|', 'WE8HP', 'WE8HP') from mike2;
select convert('dv|', 'F7DEC', 'WE8HP') from mike2;
select convert('dv|', 'WE8EBCDIC500', 'WE8HP') from mike2;
select convert('dv|', 'WE8PC850', 'WE8HP') from mike2;
select convert('dv|', 'WE8ISO8859P1', 'WE8HP') from mike2;
select convert('dv|', 'JA16EUC', 'WE8HP') from mike2;
select convert('d|v', 'US7ASCII', 'F7DEC') from mike2;
select convert('dv|', 'WE8DEC', 'F7DEC') from mike2;
select convert('dv|', 'WE8HP', 'F7DEC') from mike2;
select convert('dv|', 'F7DEC', 'F7DEC') from mike2;
select convert('dv|', 'WE8EBCDIC500', 'F7DEC') from mike2;
select convert('dv|', 'WE8PC850', 'F7DEC') from mike2;
select convert('dv|', 'WE8ISO8859P1', 'F7DEC') from mike2;
select convert('dv|', 'JA16EUC', 'F7DEC') from mike2;

select convert('d|v', 'US7ASCII', 'WE8EBCDIC500') from mike2;
select convert('dv|', 'WE8DEC', 'WE8EBCDIC500') from mike2;
select convert('dv|', 'WE8HP', 'WE8EBCDIC500') from mike2;
select convert('dv|', 'F7DEC', 'WE8EBCDIC500') from mike2;
select convert('dv|', 'WE8EBCDIC500', 'WE8EBCDIC500') from mike2;
select convert('dv|', 'WE8PC850', 'WE8EBCDIC500') from mike2;

select convert('dv|', 'WE8ISO8859P1', 'WE8EBCDIC500') from mike2; select convert('dv|', 'JA16EUC', 'WE8EBCDIC500') from mike2;

select convert('d|v', 'US7ASCII', 'WE8PC850') from mike2;
select convert('dv|', 'WE8DEC', 'WE8PC850') from mike2;
select convert('dv|', 'WE8HP', 'WE8PC850') from mike2;
select convert('dv|', 'F7DEC', 'WE8PC850') from mike2;
select convert('dv|', 'WE8EBCDIC500', 'WE8PC850') from mike2;
select convert('dv|', 'WE8PC850', 'WE8PC850') from mike2;
select convert('dv|', 'WE8ISO8859P1', 'WE8PC850') from mike2;
select convert('dv|', 'JA16EUC', 'WE8PC850') from mike2;

select convert('d|v', 'US7ASCII', 'WE8ISO8859P1') from mike2;
select convert('dv|', 'WE8DEC', 'WE8ISO8859P1') from mike2;
select convert('dv|', 'WE8HP', 'WE8ISO8859P1') from mike2;
select convert('dv|', 'F7DEC', 'WE8ISO8859P1') from mike2;
select convert('dv|', 'WE8EBCDIC500', 'WE8ISO8859P1') from mike2;
select convert('dv|', 'WE8PC850', 'WE8ISO8859P1') from mike2;

select convert('dv|', 'WE8ISO8859P1', 'WE8ISO8859P1') from mike2; select convert('dv|', 'JA16EUC', 'WE8ISO8859P1') from mike2;

select convert('d|v', 'US7ASCII', 'JA16EUC') from mike2;
select convert('dv|', 'WE8DEC', 'JA16EUC') from mike2;
select convert('dv|', 'WE8HP', 'JA16EUC') from mike2;
select convert('dv|', 'F7DEC', 'JA16EUC') from mike2;
select convert('dv|', 'WE8EBCDIC500', 'JA16EUC') from mike2;
select convert('dv|', 'WE8PC850', 'JA16EUC') from mike2;
select convert('dv|', 'WE8ISO8859P1', 'JA16EUC') from mike2;
select convert('dv|', 'JA16EUC', 'JA16EUC') from mike2;

	Of course, between ' and ' you should see the umlaut.

	The results are quite different but not as expected.

===========================================================================
| Michael Rieder               |                                          |
| Union Bank of Switzerland    | voice : +41 1 236 65 77                  |
| OREI/ORIF-RMC                | fax   :                                  |
| Bahnhofstrasse 45            | e-mail: Michael.Rieder_at_zh014.ubs.ubs.ch  |
| 8021 Zurich                  |                                          |
| Switzerland                  |                                          |
===========================================================================

Received on Tue Oct 26 1993 - 08:32:52 CET