PL/SQL Apply Same Functions More Than Once

Question

There is an encoding problem at existing Oracle database. From Java side, I apply these and fix it:

textToEscape = textToEscape.replace(/ö/g, 'ö');
textToEscape = textToEscape.replace(/ç/g, 'ç');
textToEscape = textToEscape.replace(/ü/g, 'ü');
textToEscape = textToEscape.replace(/ÅŸ/g, 'ş');
textToEscape = textToEscape.replace(/Ä/g, 'ğ');

There is a procedure which retrieves data from database. I want to write a function and apply that replace sequence inside it. I found that link:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions134.htm

However I want to apply consequent replaces. How can I chain them?

Answer 1

you can use Oracle CONVERT function to convert data into correct character set (compatible with your JAVA charset) inside database procedure itself.

That should handle all cases for you.

Answer 2

Assuming your database character set is AL32UTF8, The malformed characters that you see stem from a repeated conversion of an 8-bit character set encoding (presumably iso-8859-9 [Turkish]) to unicode in the utf-8 representation. The second of these conversions, of course, has been applied erroneously to the byte sequence that constituted the valis utf representation of your data.

You can reverse this within the database using the utl_raw package. Say tab.col contains your data, the following statement rectifies it.

update tab set col = utl_raw.cast_to_varchar2 ( utl_raw.convert ( utl_raw.cast_to_raw ( col ), 'WE8ISO8859P9', 'AL32UTF8' ) );

The casts retag the type of the character data which effectively allows for operating on the underlying octet (byte) sequence. on this level, the eroneus utf-8 mapping is invereted. since the result is still a valid representation in the database character set, a simple re-cast delivers the result.