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.
