Using unicode characters in PL/SQL

Sometimes you need to use unicode characters in messages / strings you output from PL/SQL code. However sometimes the automatic installation procedures of a customer might use a different or weird character-set when installing your code into the database. What happens is that the character within the source (in a constant for example) becomes mangled and appears as a “character not recognised question mark” in the output.

If you run into this problem there is a simple solution, replace the character in the constant / output string by using the unistr function.

Example to replace EURO-sign (added as \EURO) in a constant:

l_output_str := replace(g_output_constant, '\EURO', unistr('\20AC'));

This a fairly easy and quick method to incorporate an unicode character.