Text from another system arrives as bytes in that system's character set, and has to be turned into the bytes another system expects. UTL_RAW.CONVERT re-encodes a RAW value from one character set to another, and UTL_I18N converts between text and bytes in a named character set.
Code for This Guide
The main examples are in the examples/pkg-raw-text folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
utl_raw.convert(r, to_charset, from_charset) -- 'LANGUAGE_TERRITORY.CHARSET' utl_i18n.string_to_raw(text, charset) utl_i18n.raw_to_char(r, charset) utl_raw.translate(r, from_set, to_set) -- byte-by-byte replacement
UTF-8 to Latin-1 and Back
Example:
declare
v_utf8 raw(100) := utl_i18n.string_to_raw('Zürich', 'AL32UTF8');
v_latin1 raw(100);
begin
v_latin1 := utl_raw.convert(v_utf8, 'AMERICAN_AMERICA.WE8ISO8859P1',
'AMERICAN_AMERICA.AL32UTF8');
dbms_output.put_line('UTF-8: ' || rawtohex(v_utf8));
dbms_output.put_line('Latin-1: ' || rawtohex(v_latin1));
dbms_output.put_line('back: ' || utl_i18n.raw_to_char(v_latin1, 'WE8ISO8859P1'));
dbms_output.put_line('translate: ' || utl_raw.cast_to_varchar2(
utl_raw.translate(utl_raw.cast_to_raw('A6-NAB'), '2D', '5F')));
end;
/Output:
UTF-8: 5AC3BC72696368 Latin-1: 5AFC72696368 back: Zürich translate: A6_NAB PL/SQL procedure successfully completed.
Zürich is seven bytes in UTF-8, with ü as C3BC, and six in Latin-1, with ü as FC. RAW_TO_CHAR reads the Latin-1 bytes back as text. TRANSLATE replaces bytes one for one: here every hyphen, 2D, becomes an underscore, 5F.
Characters the Target Cannot Hold
Example:
declare
v_utf8 raw(100) := utl_i18n.string_to_raw('Zürich 5€', 'AL32UTF8');
v_ascii raw(100);
begin
v_ascii := utl_raw.convert(v_utf8, 'AMERICAN_AMERICA.US7ASCII',
'AMERICAN_AMERICA.AL32UTF8');
dbms_output.put_line('US7ASCII: ' || rawtohex(v_ascii) || ' = '
|| utl_raw.cast_to_varchar2(v_ascii));
end;
/Output:
US7ASCII: 5A757269636820353F = Zurich 5? PL/SQL procedure successfully completed.
US7ASCII has no ü and no euro sign. The ü becomes a plain u and the € a question mark, 3F, without any error, so check text that matters before converting it to a smaller character set.
Things to Know
- CONVERT needs the NLS_LANG form, language_territory.charset; the character set part decides the bytes.
- For CLOBs and BLOBs, use DBMS_LOB.CONVERTTOBLOB and CONVERTTOCLOB instead.
- UTL_I18N.VALIDATE_CHARACTER_ENCODING tells whether text has invalid characters.
Related Guides
Conclusion
UTL_RAW.CONVERT re-encodes bytes from one character set to another, and UTL_I18N turns text into bytes and back in a named character set. Convert at the edge where data enters or leaves the database, and watch for characters the target set cannot hold.
See my articles more often when you search Google.
