Text placed in HTML or XML must have characters such as <, &, and quotes turned into references, or the page breaks and becomes open to script injection. UTL_I18N.ESCAPE_REFERENCE does that, UNESCAPE_REFERENCE reverses it, and the rest of the package answers locale questions: currencies, ISO locales, linguistic sorts, and time zones.
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_i18n.escape_reference(text, page_charset) utl_i18n.unescape_reference(text) utl_i18n.get_default_iso_currency(territory) utl_i18n.map_locale_to_iso(language, territory) utl_i18n.get_local_time_zones(territory)
Escape, Unescape, and Locale Data
Example:
select utl_i18n.escape_reference('Fares < $500 & "no fees"', 'us7ascii') as escaped,
utl_i18n.unescape_reference('Zürich & Genève') as unescaped;
select utl_i18n.get_default_iso_currency('JAPAN') as japan_currency,
utl_i18n.map_locale_to_iso('GERMAN', 'SWITZERLAND') as iso_locale,
utl_i18n.get_default_linguistic_sort('FRENCH') as french_sort,
utl_i18n.get_max_character_size('AL32UTF8') as max_bytes;
declare
v_zones utl_i18n.string_array := utl_i18n.get_local_time_zones('AUSTRALIA');
i pls_integer := v_zones.first;
begin
while i is not null loop
dbms_output.put_line(v_zones(i));
i := v_zones.next(i);
end loop;
end;
/Output:
ESCAPED UNESCAPED ____________________________________________ __________________ Fares < $500 & "no fees" Zürich & Genève JAPAN_CURRENCY ISO_LOCALE FRENCH_SORT MAX_BYTES _________________ _____________ ______________ ____________ JPY de_CH FRENCH_M 4 Australia/Sydney Australia/Hobart Australia/Brisbane Australia/Adelaide Australia/Darwin Australia/Perth PL/SQL procedure successfully completed.
- ESCAPE_REFERENCE turns <, &, and the double quotes into <, &, and ".
- UNESCAPE_REFERENCE turns both numeric references such as ü and named ones such as è back into characters.
- Japan's default currency is JPY, German in Switzerland is the ISO locale de_CH, French sorts with FRENCH_M, and AL32UTF8 characters take up to 4 bytes.
- GET_LOCAL_TIME_ZONES lists the time zones of a territory.
Check for Invalid Characters
VALIDATE_CHARACTER_ENCODING returns 0 for valid text, or the position of the first invalid character.
Example:
-- 0 = valid; otherwise the position of the first invalid character
select utl_i18n.validate_character_encoding('Zürich') as text_ok,
utl_i18n.validate_character_encoding(
utl_i18n.raw_to_char(hextoraw('5AFC72696368'), 'AL32UTF8')) as latin1_as_utf8;Output:
TEXT_OK LATIN1_AS_UTF8
__________ _________________
0 2Latin-1 bytes read as UTF-8 make an invalid character at position 2, where FC is not a valid UTF-8 sequence. This catches text that arrived with the wrong character set.
Things to Know
- Escape every value you put into HTML or XML, not only the ones you expect to contain special characters.
- Escaping for HTML is not the same as escaping for URLs or JSON; use UTL_URL and JSON functions for those.
- Locale names use Oracle's NLS names, such as GERMAN and SWITZERLAND.
Related Guides
Conclusion
UTL_I18N escapes and unescapes HTML and XML references, validates character encodings, and answers locale questions about currencies, ISO locales, sorts, and time zones. Escape everything you put into markup, and validate text that comes from outside.
