Oracle UTL_I18N
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Note: Internationalization is often abbreviated as I18N (or i18n or I18n) where the number 18 refers to the number of letters omitted.
AUTHID DEFINER
Constants
Name Data Type Value
Encode SQL-XML Function
XMLTAG_TO_SQLNAME PLS_INTEGER 0
SQLNAME_TO_XMLTAG PLS_INTEGER 1
General: Used with SHIFT_STATUS
SHIFT_IN PLS_INTEGER 0
SHIFT_OUT PLS_INTEGER 1
Locale Map Flaggings
GENERIC_CONTEXT PLS_INTEGER 0
IANA_TO_ORACLE PLS_INTEGER 1
MAIL_CONTEXT PLS_INTEGER 1
MAIL_GENERIC PLS_INTEGER 0
MAIL_WINDOWS PLS_INTEGER 1
ORACLE_TO_IANA PLS_INTEGER 0
Translation Flags
LANGUAGE_TRANS PLS_INTEGER 0
TERRITORY_TRANS PLS_INTEGER 1
LANGUAGE_TERRITORY_TRANS PLS_INTEGER 2
Transliteration Constants
KANA_FWKATAKANA VARCHAR2(30) 'kana_fwkatakana'
KANA_HWKATAKANA VARCHAR2(30) 'kana_hwkatakana'
KANA_HIRAGANA VARCHAR2(30) 'kana_hiragana'
FWKATAKANA_HWKATAKANA VARCHAR2(30) 'fwkatakana_hwkatakana'
FWKATAKANA_HIRAGANA VARCHAR2(30) 'fwkatakana_hiragana'
HWKATAKANA_FWKATAKANA VARCHAR2(30) 'hwkatakana_fwkatakana'
HWKATAKANA_HIRAGANA VARCHAR2(30) 'hwkatakana_hiragana'
HIRAGANA_FWKATAKANA VARCHAR2(30) hiragana_fwkatakana'
HIRAGANA_HWKATAKANA VARCHAR2(30) 'hiragana_hwkatakana'
Data Types TYPE string_array IS TABLE of VARCHAR2(32767)
INDEX BY BINARY_INTEGER;
Dependencies
DBMS_BLOCKCHAIN_TABLE DBMS_REPORT SPARQL_SERVICE_IMPL_T
DBMS_CUBE DBMS_SCHEDULER UTL_I18_LIB
DBMS_ISCHED HTF UTL_RAW
DBMS_ISCHED_REMOTE_ACCESS PLITBLM UTL_SMTP
Documented Yes
Exceptions
Error Code Reason
ORA-01722 Invalid number: escaping format is invalid
ORA-27102 Out of memory
ORA-03001 Unsupported Transliteration
First Available 10.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utli18n.sql
Subprograms
 
ENCODE_SQL_XML
Converts between XML name and a SQL identifier utl_i18n.encode_sql_xml(
name IN VARCHAR2 CHARACTER SET ANY_CS,
flag IN PLS_INTEGER DEFAULT XMLTAG_TO_SQLNAME)
RETURN VARCHAR2 CHARACTER SET name%CHARSET;
SQL> SELECT utl_i18n.encode_sql_xml('_xFFFF_',1)
  2  FROM dual;

UTL_I18N.ENCODE_SQL_XML('_XFFFF_',1)
-----------------------------------------
_x005F_xFFFF_

SQL> SELECT utl_i18n.encode_sql_xml('_xHHHHHHHH_',1)
FROM dual;

UTL_I18N.ENCODE_SQL_XML('_XHHHHHHHH_',1)
-----------------------------------------
_x005F_xHHHHHHHH_
 
ESCAPE_REFERENCE
Converts a given text string to its character reference counterparts, for characters that fall outside the document character set utl_i18n.escape_reference(
str          IN VARCHAR2 CHARACTER SET ANY_CS,
page_cs_name IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
SQL> SELECT utl_i18n.escape_reference('UW' || CHR(150),'US7ASCII')
  2  FROM dual;

UTL_I18N.ESCAPE_REFERENCE('UW'||CHR(150),'US7ASCII')
----------------------------------------------------
UW
 
GET_COMMON_TIME_ZONES
Returns the list of common time zone IDs that are independent of the locales utl_i18n.get_common_time_zones RETURN string_array;
set serveroutput on

DECLARE
 retval utl_i18n.string_array;
 cnt    PLS_INTEGER;
BEGIN
  retval := utl_i18n.get_common_time_zones;
  dbms_output.put('Count = ');
  dbms_output.put_line(retval.LAST-retval.FIRST+1);
  cnt := retval.FIRST;

  WHILE cnt IS NOT NULL LOOP
    dbms_output.put_line(retval(cnt));
    cnt := retval.NEXT(cnt);
  END LOOP;
END;
/
 
GET_DEFAULT_CHARSET
Returns the default Oracle character set name or the default e-mail safe character set name from an Oracle language name utl_i18n.get_default_charset(
language  IN VARCHAR2,
context   IN PLS_INTEGER DEFAULT GENERIC_CONTEXT,
iswindows IN BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2;
SQL> SELECT utl_i18n.get_default_charset('English', 0)
  2  FROM dual;

UTL_I18N.GET_DEFAULT_CHARSET('ENGLISH',0)
------------------------------------------
WE8ISO8859P1

SQL> SELECT utl_i18n.get_default_charset('Japanese', 0)
FROM dual;

UTL_I18N.GET_DEFAULT_CHARSET('JAPANESE',0)
-------------------------------------------
JA16EUC
 
GET_DEFAULT_ISO_CURRENCY
Returns the default ISO 4217 currency code for the specified territory utl_i18n.get_default_iso_currency(territory IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT utl_i18n.get_default_iso_currency('America')
  2  FROM dual;

UTL_I18N.GET_DEFAULT_ISO_CURRENCY('AMERICA')
---------------------------------------------
USD

SQL> SELECT utl_i18n.get_default_iso_currency('Japan')
  2 FROM dual;

UTL_I18N.GET_DEFAULT_ISO_CURRENCY('JAPAN')
-------------------------------------------
JPY
 
GET_DEFAULT_LINGUISTIC_SORT
Returns the default linguistic sort name for the specified language utl_i18n.get_default_linguistic_sort(language IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT utl_i18n.get_default_linguistic_sort('German')
  2  FROM dual;

UTL_I18N.GET_DEFAULT_LINGUISTIC_SORT('GERMAN')
-----------------------------------------------
GENERIC_M
 
GET_LOCAL_LANGUAGES
Returns the local language names for the specified territory utl_i18n.get_local_languages(language IN VARCHAR2) RETURN string_array;
set serveroutput on

SQL> DECLARE
  2   retval utl_i18n.string_array;
  3   cnt    PLS_INTEGER;
  4  BEGIN
  5    retval := utl_i18n.get_local_languages('SWITZERLAND');
  6    dbms_output.put('Count = ');
  7    dbms_output.put_line(retval.LAST);
  8    cnt := retval.FIRST;
  9
 10    WHILE cnt IS NOT NULL LOOP
 11      dbms_output.put_line(retval(cnt));
 12      cnt := retval.NEXT(cnt);
 13    END LOOP;
 14  END;
 15  /
Count = 2
GERMAN
FRENCH
ITALIAN

PL/SQL procedure successfully completed.
 
GET_LOCAL_LINGUISTIC_SORTS
Returns the local linguistic sort names for the specified language

Thank you Michel Cadot for multiple corrections on this page.
utl_i18n.get_local_linguistic_sorts(language IN VARCHAR2) RETURN string_array;
SQL> DECLARE
  2   retval utl_i18n.string_array;
  3   cnt    PLS_INTEGER;
  4  BEGIN
  5    retval := utl_i18n.get_local_linguistic_sorts('American');
  6    dbms_output.put('Count = ');
  7    dbms_output.put_line(retval.LAST-retval.FIRST+1);
  8    cnt := retval.FIRST;
  9    WHILE cnt IS NOT NULL LOOP
 10      dbms_output.put_line(retval(cnt));
 11      cnt := retval.NEXT(cnt);
 12    END LOOP;
 13* END;
 14  /
Count = 2
GENERIC_M
BINARY

PL/SQL procedure successfully completed.

SQL> DECLARE
  2   retval utl_i18n.string_array;
  3   cnt PLS_INTEGER;
  4  BEGIN
  5    retval := utl_i18n.get_local_linguistic_sorts('Simplified Chinese');
  6    dbms_output.put('Count = ');
  7    dbms_output.put_line(retval.LAST-retval.FIRST+1);
  8    cnt := retval.FIRST;
  9    WHILE cnt IS NOT NULL LOOP
 10      dbms_output.put_line(retval(cnt));
 11      cnt := retval.NEXT(cnt);
 12    END LOOP;
 13* END;
 14  /
Count = 5
SCHINESE_PINYIN_M
SCHINESE_STROKE_M
SCHINESE_RADICAL_M
GBK
BINARY
 
GET_LOCAL_TERRITORIES
Returns the local territory names for the specified language utl_i18n.get_local_territories(language IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN string_array;
set serveroutput on

SQL> DECLARE
  2   retVal utl_i18n.string_array;
  3   cnt    PLS_INTEGER;
  4  BEGIN
  5    retval := utl_i18n.get_local_territories('ENGLISH');
  6    dbms_output.put('Count = ');
  7    dbms_output.put_line(retval.LAST-retval.FIRST+1);
  8    cnt := retval.FIRST;
  9    WHILE cnt IS NOT NULL LOOP
 10      dbms_output.put_line(retval(cnt));
 11      cnt := retval.NEXT(cnt);
 12    END LOOP;
 13* END;
 14  /
Count = 36
UNITED KINGDOM
AMERICA
ANTIGUA AND BARBUDA
AUSTRALIA
BAHAMAS
BARBADOS
BELIZE
BERMUDA
BOTSWANA
CANADA
CAYMAN ISLANDS
DOMINICA
GHANA
GRENADA
GUYANA
HONG KONG
INDIA
IRELAND
JAMAICA
MALAWI
MAURITIUS
NAMIBIA
NEW ZEALAND
NIGERIA
PAKISTAN
SAINT KITTS AND NEVIS
SAINT LUCIA
SIERRA LEONE
SINGAPORE
SOUTH AFRICA
SOUTH SUDAN
SWAZILAND
TRINIDAD AND TOBAGO
UGANDA
ZAMBIA
ZIMBABWE

PL/SQL procedure successfully completed.
 
GET_LOCAL_TIME_ZONES
Returns the local time zone IDs for the specified territory utl_i18n.get_local_time_zones(territory IN VARCHAR2) RETURN string_array;
set serveroutput on

SQL> DECLARE
  2   retVal utl_i18n.string_array;
  3   cnt    PLS_INTEGER;
  4  BEGIN
  5    retVal := utl_i18n.get_local_time_zones('AMERICA');
  6    dbms_output.put('Count = ');
  7    dbms_output.put_line(retVal.LAST-retVal.FIRST+1);
  8    cnt := retVal.FIRST;
  9
 10    WHILE cnt IS NOT NULL LOOP
 11      dbms_output.put_line(retval(cnt));
 12      cnt := retval.NEXT(cnt);
 13    END LOOP;
 14  END;
 15   /
Count = 8
America/New_York
America/Indianapolis
America/Chicago
America/Denver
America/Phoenix
America/Los_Angeles
America/Anchorage
Pacific/Honolulu

PL/SQL procedure successfully completed.
 
GET_MAX_CHARACTER_SIZE
Returns the maximum number of bytes in a character set's characters utl_i18n.get_max_character_size(charset_name IN VARCHAR2) RETURN BINARY_INTEGER;
SQL> SELECT utl_i18n.get_max_character_size('US7ASCII')
  2  FROM dual;

UTL_I18N.GET_MAX_CHARACTER_SIZE('US7ASCII')
-------------------------------------------
                                          1

SQL> SELECT utl_i18n.get_max_character_size('JA16EUC')
  2  FROM dual;

UTL_I18N.GET_MAX_CHARACTER_SIZE('JA16EUC')
------------------------------------------
                                         3

SQL> SELECT utl_i18n.get_max_character_size('AL32UTF8')
  2  FROM dual;

UTL_I18N.GET_MAX_CHARACTER_SIZE('AL32UTF8')
-------------------------------------------
                                          4
 
GET_TRANSLATION
Returns the translation of the language and territory name in the specified translation language utl_i18n.get_translation(
param1         IN VARCHAR2 CHARACTER SET ANY_CS,
trans_language IN VARCHAR2    DEFAULT 'AMERICAN',
flag           IN PLS_INTEGER DEFAULT LANGUAGE_TRANS)
RETURN VARCHAR2 CHARACTER SET param1%CHARSET;
set linesize 121
col parameter format a20
col value format a20

SELECT * FROM gv$nls_valid_values;

SQL> SELECT utl_i18n.get_translation('SIMPLIFIED CHINESE', 'ITALIAN')
  2  FROM dual;

UTL_I18N.GET_TRANSLATION('SIMPLIFIEDCHINESE','ITALIAN')
-------------------------------------------------------
Cinese semplificato

SQL> SELECT utl_i18n.get_translation('AMERICAN', 'SPANISH')
  2  FROM dual;

UTL_I18N.GET_TRANSLATION('AMERICAN','SPANISH')
----------------------------------------------
Ingles Americano

SQL> SELECT utl_i18n.get_translation('SIMPLIFIED CHINESE', 'GERMAN')
  2  FROM dual;

UTL_I18N.GET_TRANSLATION('SIMPLIFIEDCHINESE','GERMAN')
------------------------------------------------------
Vereinfachtes Chinesisch

SQL> SELECT utl_i18n.get_translation('SIMPLIFIED CHINESE', 'SWEDISH')
  2  FROM dual;

UTL_I18N.GET_TRANSLATION('SIMPLIFIEDCHINESE','SWEDISH')
-------------------------------------------------------
Forenklad kinesiska

SQL> SELECT utl_i18n.get_translation('AMERICAN', 'DANISH')
  2  FROM dual;

UTL_I18N.GET_TRANSLATION('AMERICAN','DANISH')
---------------------------------------------
Amerikansk
 
MAP_CHARSET
Maps an Oracle character set name to an IANA character set name

Maps an IANA character set name to an Oracle character set name

Maps an Oracle character set to an e-mail safe character set name
utl_i18n.map_charset(
charset IN VARCHAR2,
context IN PLS_INTEGER DEFAULT GENERIC_CONTEXT,
flag    IN PLS_INTEGER DEFAULT ORACLE_TO_IANA)
RETURN VARCHAR2;
SQL> SELECT utl_i18n.map_charset('iso-8859-1', 0, 1)
  2  FROM dual;

UTL_I18N.MAP_CHARSET('ISO-8859-1',0,1)
--------------------------------------
WE8ISO8859P1

SQL> SELECT utl_i18n.map_charset('iso-8859-1', 1, 0)
  2  FROM dual;

UTL_I18N.MAP_CHARSET('ISO-8859-1',1,0)
--------------------------------------
AL32UTF8
 
MAP_FROM_SHORT_LANGUAGE
Maps an Oracle short language name to its full  language name utl_i18n.map_territory_from_short_language(language IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT utl_i18n.map_from_short_language('GB')
  2  FROM dual;

UTL_I18N.MAP_FROM_SHORT_LANGUAGE('GB')
--------------------------------------
ENGLISH

SQL> SELECT utl_i18n.map_from_short_language('SW')
  2  FROM dual;

UTL_I18N.MAP_FROM_SHORT_LANGUAGE('SW')
--------------------------------------
SWAHILI
 
MAP_LANGUAGE_FROM_ISO
Returns an Oracle language name from an ISO locale name utl_i18n.map_language_from_iso(isolocale IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT utl_i18n.map_language_from_iso('en_US')
  2  FROM dual;

UTL_I18N.MAP_LANGUAGE_FROM_ISO('EN_US')
---------------------------------------
AMERICAN
 
MAP_LOCALE_TO_ISO
Returns an ISO locale name from an Oracle language name and an Oracle territory name utl_i18n.map_locale_to_iso(
ora_language  IN VARCHAR2,
ora_territory IN VARCHAR2)
RETURN VARCHAR2;
SQL> SELECT utl_i18n.map_locale_to_iso('American', 'America')
  2  FROM dual;

UTL_I18N.MAP_LOCALE_TO_ISO('AMERICAN','AMERICA')
------------------------------------------------
en_US
 
MAP_TERRITORY_FROM_ISO
Returns an Oracle territory name from an ISO locale utl_i18n.map_territory_from_iso(isolocale IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT utl_i18n.map_territory_from_iso('en_US')
  2  FROM dual;

UTL_I18N.MAP_TERRITORY_FROM_ISO('EN_US')
-----------------------------------------
AMERICA
 
MAP_TO_SHORT_LANGUAGE
Maps an Oracle full language name to short language name utl_i18n.map_territory_to_short_language(language IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT utl_i18n.map_to_short_language('ENGLISH')
  2  FROM dual;

UTL_I18N.MAP_TO_SHORT_LANGUAGE('ENGLISH')
-----------------------------------------
GB
 
RAW_TO_CHAR
Convert RAW to a string of type CHAR

Overload 1
utl_i18n.raw_to_char(
data        IN RAW,
src_charset IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
SQL> SELECT utl_i18n.raw_to_char('44616E204D6F7267616E','AL32UTF8')
  2  FROM dual;

UTL_I18N.RAW_TO_CHAR('44616E204D6F7267616E','AL32UTF8')
--------------------------------------------------------
Dan Morgan
Overload 2 utl_i18n.raw_to_char(
data            IN     RAW,
src_charset     IN     VARCHAR2 DEFAULT NULL,
scanned_length     OUT PLS_INTEGER,
shift_status    IN OUT PLS_INTEGER)
RETURN VARCHAR2;
SQL> set serveroutput on

SQL> DECLARE
  2   scnlen PLS_INTEGER;
  3   shstat PLS_INTEGER := 1;
  4   retval VARCHAR2(30);
  5  BEGIN
  6    retval := utl_i18n.raw_to_char('44616E204D6F7267616E', 'AL32UTF8', scnlen, shstat);
  7    dbms_output.put_line(retval);
  8    dbms_output.put_line(scnlen);
  9    dbms_output.put_line(shstat);
 10  END;
 11  /
Dan Morgan
10
0

PL/SQL procedure successfully completed.
 
RAW_TO_NCHAR
Convert RAW to a string of type NCHAR

Overload 1
utl_i18n.raw_to_nchar(
data        IN RAW,
src_charset IN VARCHAR2 DEFAULT NULL)
RETURN NVARCHAR2;
SQL> SELECT utl_i18n.raw_to_nchar('44616E204D6F7267616E','AL32UTF8')
  2  FROM dual;

UTL_I18N.RAW_TO_NCHAR('44616E204D6F7267616E','AL32UTF8')
---------------------------------------------------------
Dan Morgan
Overload 2 utl_i18n.raw_to_nchar(
data           IN     RAW,
src_charset    IN     VARCHAR2 DEFAULT NULL,
scanned_length    OUT PLS_INTEGER,
shift_status   IN OUT PLS_INTEGER)
RETURN NVARCHAR2;
SQL> set serveroutput on

SQL> DECLARE
  2   scnlen PLS_INTEGER;
  3   shstat PLS_INTEGER := 3;
  4   retval VARCHAR2(30);
  5  BEGIN
  6    retval := utl_i18n.raw_to_nchar('44616E204D6F7267616E', 'AL32UTF8', scnlen, shstat);
  7    dbms_output.put_line(retval);
  8    dbms_output.put_line(scnlen);
  9    dbms_output.put_line(shstat);
 10  END;
 11  /
Dan Morgan
10
0

PL/SQL procedure successfully completed.
 
STRING_TO_RAW
Convert a string to RAW utl_i18n.string_to_raw(
data        IN VARCHAR2 CHARACTER SET ANY_CS,
dst_charset IN VARCHAR2 DEFAULT NULL)
RETURN RAW;
SQL> SELECT utl_i18n.string_to_raw('Dan Morgan','AL32UTF8')
  2  FROM dual;

UTL_I18N.STRING_TO_RAW('DANMORGAN','AL32UTF8')
-----------------------------------------------
44616E204D6F7267616E
 
TRANSLITERATE
Transliterates between Japanese hiragana and katakana utl_i18n.transliterate (
data IN VARCHAR2 CHARACTER SET ANY_CS,
name IN VARCHAR2)
RETURN VARCHAR2 CHARACTER SET data%CHARSET;
set serveroutput on

DECLARE
 name japanese_emp.ename%TYPE;
 eno  CONSTANT NUMBER(4) := 1;
BEGIN
  SELECT ename
  INTO name
  FROM japanese_emp
  WHERE enumber = eno;

  name := utl_i18n.transliterate(name, utl_i18n.kana_hiragana);

  dbms_output.put_line(name);
EXCEPTION
  WHEN utl_i18n.unsupported_transliteration THEN
    dbms_output.put_line('transliteration not supported');
END;
/
 
UNESCAPE_REFERENCE
Converts an input string that contains character references to a text string utl_i18n.unescape_reference(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET str%CHARSET;
SQL> SELECT utl_i18n.unescape_reference('UW–')
  2  FROM dual;

UTL_I18N.UNESCAPE_REFERENCE('UW–')
------------------------------------------
UW?
 
VALIDATE_CHARACTER_ENCODING
Validates the character encoding of VARCHAR2/NVARCHAR2 data

Overload 1
utl_i18n.validate_character_encoding(str IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN PLS_INTEGER;
SQL> SELECT utl_i18n.validate_character_encoding('Daniel Morgan')
  2  FROM dual;

UTL_I18N.VALIDATE_CHARACTER_ENCODING('DANIELMORGAN')
----------------------------------------------------
                                                   0
Validate CLOB/NCLOB data for database/national character set

Overload 2
utl_i18n.validate_character_encoding(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN NUMBER;
SQL> DECLARE
  2   inClob CLOB := 'Morgan''s Library';
  3   retVal NUMBER;
  4  BEGIN
  5    retVal := utl_i18n.validate_character_encoding(inClob);
  6    dbms_output.put_line(retVal);
  7  END;
  8  /
0

PL/SQL procedure successfully completed.
 
VALIDATE_SQLNAME
Validates an oracle object name utl_i18n.validate_sqlname(name IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN PLS_INTEGER;
SQL> set define off
SQL> SELECT utl_i18n.validate_sqlname('&')
  2  FROM dual;

UTL_I18N.VALIDATE_SQLNAME('&')
-------------------------------
                             0

Related Topics
Built-in Functions
Built-in Packages
Character Sets
Character Set Functions
DBMS_CRYPTO
UTL_GDK
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx