characterset converting 1

[tomvdduin]

Hi all!

Our program based on a Oracle database makes an export of some data in de DB on a windows machine. One of our clients saves that file on a fileserver, opens it in a mac and reads it into Quark. Now all the characters with a ASCII value above 127 are changed, because the mac has a different characterset than windows.

The customer doesn't want to buy a program like 'maclink', that converts the files, so I want to alter the procedure, to change the characterset.

I know I can use convert(), but I don't know witch charactersets Windows and Mac uses. Does anyone know?

another option is to manually replace all characters. now the following problem appears: The DB uses WE8MACROMAN8 characterset, so I can't do: replace('à',chr(224),chr(136)), because à doesn't have ascii-value 224 in WE8MACROMAN8. 224 is the windows-ascii value.

Can someone help me with this?

greetz,

Tom

 

[SantaMufasa]

Tom,

If you problem is that you need different characters output from Oracle, we can build a simple function that you need. To do so, please post a two-column list of the values you want: Windows/Oracle character versus Output/Mac character. please use ASCII-numeric values, not "visual" symbols: Example: show CHR(224), not "à".

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:02 (10Dec04) UTC (aka "GMT" and "Zulu"),
@ 10:02 (10Dec04) Mountain Time

 

[tomvdduin]

Hi Mufasa,

in general the characters in the following bitmap has to be converted:

http://www1.tip.nl/~t876506/Comn1.gif,

with two additions:
character ® (windows: chr(174), Mac: chr(168)
character ™ (windows: chr(0153) Mac: chr(170)

Thanks for your help! I really appreciate!

greetz,

Tom

 

[tomvdduin]

Hmm, in the last post of me I posted a bitmap... now you have to type it all out... here's a list in text-format, so you can copy-past!

chr(192),chr(203)
chr(224),chr(136)
chr(193),chr(231)
chr(225),chr(135)
chr(194),chr(229)
chr(226),chr(137)
chr(195),chr(204)
chr(227),chr(139)
chr(196),chr(128)
chr(228),chr(138)
chr(197),chr(129)
chr(229),chr(140)
chr(198),chr(174)
chr(230),chr(190)
chr(199),chr(130)
chr(231),chr(141)
chr(200),chr(233)
chr(232),chr(143)
chr(201),chr(131)
chr(233),chr(142)
chr(202),chr(230)
chr(234),chr(144)
chr(203),chr(232)
chr(235),chr(145)
chr(204),chr(237)
chr(236),chr(147)
chr(205),chr(234)
chr(237),chr(146)
chr(206),chr(235)
chr(238),chr(148)
chr(207),chr(236)
chr(239),chr(149)
chr(209),chr(132)
chr(241),chr(150)
chr(210),chr(241)
chr(242),chr(152)
chr(211),chr(238)
chr(243),chr(151)
chr(212),chr(239)
chr(244),chr(153)
chr(213),chr(205)
chr(245),chr(155)
chr(214),chr(133)
chr(246),chr(154)
chr(216),chr(175)
chr(248),chr(191)
chr(140),chr(206)
chr(156),chr(207)
chr(223),chr(167)
chr(217),chr(244)
chr(249),chr(157)
chr(218),chr(242)
chr(250),chr(156)
chr(219),chr(243)
chr(251),chr(158)
chr(220),chr(135)
chr(252),chr(159)
chr(159),chr(217)
chr(225),chr(216)

chr(168),chr(174)
chr(170),chr(153)

I also thried the function convert(string,'WE8MSWIN1252','WE8MACROMAN8S'), but not all characters are correctly converted, although some are.

Again, thanks in advance!

 

[tomvdduin]

Hmm, it 's a bit early here, in The Netherlands. forgot to tell you witch column is the windows column...

the first column is the windows ascii-value, the second column is the mac-ascii value

 

[SantaMufasa]

Tom,

Sorry that I didn't get this solution to you during your normal business hours, but for some reason, my customers that pay in something beside Purple Stars think they should take priority over Tek-Tipsters. In any case, here is a solution that should be very simple to use:

Section 1 -- Sample invocation and results:

col a heading "Sample character transformations" format a50
select 'Win vals.: [Ëç宾éæèíêëìñîͧôòóØ®] equal these'||chr(10)||
       'Mac vals.: ['||chrset.win2mac('Ëç宾éæèíêëìñîͧôòóØ®')||']' a
from dual;

Sample character transformations
----------------------------------------------
Win vals.: [Ëç宾éæèíêëìñîͧôòóØ®] equal these
Mac vals.: [ÀÁ¨æÈÊËÌÍÎÏÒÓÕßÙÚÛá¨]

Section 2 -- Sample code translations (that you gave me), placed in a table:

create table chrset_conv (mac char(1),win char(1));
insert into chrset_conv values (chr(192),chr(203));
insert into chrset_conv values (chr(224),chr(136));
insert into chrset_conv values (chr(193),chr(231));
insert into chrset_conv values (chr(225),chr(135));
insert into chrset_conv values (chr(194),chr(229));
insert into chrset_conv values (chr(226),chr(137));
insert into chrset_conv values (chr(195),chr(204));
insert into chrset_conv values (chr(227),chr(139));
insert into chrset_conv values (chr(196),chr(128));
insert into chrset_conv values (chr(228),chr(138));
insert into chrset_conv values (chr(197),chr(129));
insert into chrset_conv values (chr(229),chr(140));
insert into chrset_conv values (chr(198),chr(174));
insert into chrset_conv values (chr(230),chr(190));
insert into chrset_conv values (chr(199),chr(130));
insert into chrset_conv values (chr(231),chr(141));
insert into chrset_conv values (chr(200),chr(233));
insert into chrset_conv values (chr(232),chr(143));
insert into chrset_conv values (chr(201),chr(131));
insert into chrset_conv values (chr(233),chr(142));
insert into chrset_conv values (chr(202),chr(230));
insert into chrset_conv values (chr(234),chr(144));
insert into chrset_conv values (chr(203),chr(232));
insert into chrset_conv values (chr(235),chr(145));
insert into chrset_conv values (chr(204),chr(237));
insert into chrset_conv values (chr(236),chr(147));
insert into chrset_conv values (chr(205),chr(234));
insert into chrset_conv values (chr(237),chr(146));
insert into chrset_conv values (chr(206),chr(235));
insert into chrset_conv values (chr(238),chr(148));
insert into chrset_conv values (chr(207),chr(236));
insert into chrset_conv values (chr(239),chr(149));
insert into chrset_conv values (chr(209),chr(132));
insert into chrset_conv values (chr(241),chr(150));
insert into chrset_conv values (chr(210),chr(241));
insert into chrset_conv values (chr(242),chr(152));
insert into chrset_conv values (chr(211),chr(238));
insert into chrset_conv values (chr(243),chr(151));
insert into chrset_conv values (chr(212),chr(239));
insert into chrset_conv values (chr(244),chr(153));
insert into chrset_conv values (chr(213),chr(205));
insert into chrset_conv values (chr(245),chr(155));
insert into chrset_conv values (chr(214),chr(133));
insert into chrset_conv values (chr(246),chr(154));
insert into chrset_conv values (chr(216),chr(175));
insert into chrset_conv values (chr(248),chr(191));
insert into chrset_conv values (chr(140),chr(206));
insert into chrset_conv values (chr(156),chr(207));
insert into chrset_conv values (chr(223),chr(167));
insert into chrset_conv values (chr(217),chr(244));
insert into chrset_conv values (chr(249),chr(157));
insert into chrset_conv values (chr(218),chr(242));
insert into chrset_conv values (chr(250),chr(156));
insert into chrset_conv values (chr(219),chr(243));
insert into chrset_conv values (chr(251),chr(158));
insert into chrset_conv values (chr(220),chr(135));
insert into chrset_conv values (chr(252),chr(159));
insert into chrset_conv values (chr(159),chr(217));
insert into chrset_conv values (chr(225),chr(216));
insert into chrset_conv values (chr(168),chr(174));
insert into chrset_conv values (chr(170),chr(153));

Section 3 -- Package and its functions to take care of your business:

create or replace package chrset is
	mac_vals	varchar2(256);
	win_vals	varchar2(256);
	function get_chrvals(domain varchar2) return varchar2;
	function win2mac(in_str varchar2) return varchar2;
	function mac2win(in_str varchar2) return varchar2;
end;
/

Package created.

create or replace package body chrset is
function get_chrvals(domain varchar2) return varchar2 is
begin
	if chrset.mac_vals||chrset.win_vals is null then
		for r in (select * from chrset_conv) loop
			chrset.mac_vals	:= chrset.mac_vals||r.mac;
			chrset.win_vals	:= chrset.win_vals||r.win;
		end loop;
	end if;
	if upper(domain) = 'MAC' then
		return chrset.mac_vals;
	elsif upper(domain) = 'WIN' then
		return chrset.win_vals;
	else
		return null;
	end if;
end;
function win2mac(in_str varchar2) return varchar2 is
begin
	return translate(in_str
		,chrset.get_chrvals('win')
		,chrset.get_chrvals('mac'));
end;
function mac2win(in_str varchar2) return varchar2 is
begin
	return translate(in_str
		,chrset.get_chrvals('mac')
		,chrset.get_chrvals('win'));
end;
end;
/

Package body created.

Amongst the neat efficiencies of this code are:

1) No matter how many times you invoke the "chrset.win2mac" or "chrset.mac2win" functions in a session, it only reads the conversion table ("chrset_conv") the first time; after that, all the conversion values persist in memory for later use that same session.

2) You can process strings up to 4000 characters per RETURN with the two "chrset" functions.

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 22:04 (13Dec04) UTC (aka "GMT" and "Zulu"),
@ 15:04 (13Dec04) Mountain Time

 

[tomvdduin]

Mufasa,

it works! you deserve a star! Thanks...

greets,

Tom

 

[SantaMufasa]

Tom,

In you post just prior to my [22:04 (13Dec04) UTC (aka "GMT" and "Zulu"] post, you said:

the first column is the windows ascii-value, the second column is the mac-ascii value

That correlation does not seem to match your earlier example:

character ® (windows: chr(174), Mac: chr(168)
character ™ )

In my solution for you, I matched the patterns between your two-column list and the ASCII patterns in you two-line example above. So, check your output to ensure that it behaves properly. If the function behaves "backwards", then re-create the Mac-Win table to become a Win-Mac table.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:27 (15Dec04) UTC (aka "GMT" and "Zulu"),
@ 10:27 (15Dec04) Mountain Time