We have some database data (Oracle 11g) that contains special characters, such as δ.
WF prints them as upside-down question marks.

Due to encoding mismatches between servers, clients and browsers, I am trying to replace those characters with their respective HTML entities. That is only a partial solution to begin with as this report also outputs as PDF, which prints HTML entities like δ verbatim!

Our WF setup is a mish-mash of Win-1252 and Cp 437 encoding to be compatible with our remote servers on AS/400 and OpenVMS respectively. Aside from that, we have some fields with long strings (A2000V) which, AFAIK, would be severly limited in size if we were to switch to UTF-8 in WF. Hence I am under the impression our encodings should stay as they are.

Initially I tried a plain character replacement, which turned up fun things like Dev Studio storing a 'δ'-character as a plain 'd'-character and thus replacing all d's in the strings with a δ entity - Doh!

Then it turned out that if I fixed the character that Dev Studio stored, it was (apparently) sending the wrong string to Oracle (I bet in the wrong encoding) as no replacement was taking place.

It turns out that I have to send the UTF-8 character as a (hexadecimal) byte-string and convert it back to a character on the Oracle side to even get the correct character as a parameter into the replace function...

Currently, I have a workaround using SQL passthru (which I was using for this query anyway) that goes like this:

SQL SQLORA PREPARE SQLOUT FOR
SELECT
-* For some reason, lowercase delta does not get escaped
	replace(
		trim(description),
-* (X)	'δ',
-* (X)	unistr(hextoraw('CEB4')),
		utl_raw.cast_to_varchar2(hextoraw('CEB4')),
		'&'||'delta;'
	) AS p_description
FROM table;
END

As you can see, all that is to replace just one special character with its HTML entity counterpart. Not very generic, for every additional replacement I need to wrap the above in another replacement. Bah!

Now that's just the start. Returning a HTML entity as a replacement is a fairly simple thing to do, but obviously that breaks in PDF output. I don't have a solution for that yet.

I'm under the impression that this ought to be simpler. Am I missing something obvious?

This message has been edited. Last edited by: <Kathryn Henning>,

Wep,

did you consider making view with in the DBMS. Perhaps even two. An view for HTML-output and view for PDF-output.

Let SQL replace those pesky characters... ( they're all Greek to me anyway ).

Good luck,
Dave