Oracle DBMS_I18N
Version 11.1
 
General Information
Note Internationalization is often abbreviated as I18N (or i18n or I18n) where the number 18 refers to the number of letters omitted.
Source {ORACLE_HOME}/rdbms/admin/utli18n.sql
Constants
Name Description Data Type Value
SHIFT_IN Used with shift_status.
Must be set the first time it is called in piecewise conversion.		 PLS_INTEGER 0
SHIFT_OUT   PLS_INTEGER 1
Defined Data Type TYPE string_array IS TABLE of VARCHAR2(32767)
INDEX BY BINARY_INTEGER;
Dependencies
DECRYPT ENCRYPT_STRING UTL_I18_LIB

V$NLS_VALID_VALUES
ENCRYPT GET_ENC_VAL UTL_RAW  
Encode SQL-XML Function Constants
Name Data Type Value
XMLTAG_TO_SQLNAME PLS_INTEGER 0
SQLNAME_TO_XMLTAG PLS_INTEGER 1
Exceptions
Error Code Reason
01722 invalid number: escaping format is invalid
03001 Unsupported Transliteration
27102 out of memory
Locale-Mapping Flags
Name Description Data Type Value
GENERIC_CONTEXT Returns the default character set for general cases. PLS_INTEGER 0
IANA_TO_ORACLE Map from an IANA character set name to an Oracle character set name. PLS_INTEGER 1
MAIL_CONTEXT The mapping is between an Oracle character set name and an email safe character set name. PLS_INTEGER 1
MAIL_GENERIC Map from an Oracle character set name to an email safe character set name on a non-Windows platform. PLS_INTEGER 0
MAIL_WINDOWS Map from an Oracle character set name to an email safe character set name on a Windows platform. PLS_INTEGER 1
ORACLE_TO_IANA Map from an Oracle character set name to an IANA character set name. PLS_INTEGER 0
Translation Flags
Name Data Type Value
LANGUAGE_TRANS PLS_INTEGER 0
TERRITORY_TRANS PLS_INTEGER 1
LANGUAGE_TERRITORY_TRANS PLS_INTEGER 2
Transliteration Constants
Name Data Type Value
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'
Object Privileges grant execute on utl_18n to <schema_name>;
grant execute on utl_18n to uwclass;
 
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;
SELECT utl_i18n.encode_sql_xml('_xFFFF_',1)
FROM dual;

SELECT utl_i18n.encode_sql_xml('_xHHHHHHHH_',1)
FROM dual;
 
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;
SELECT utl_i18n.escape_reference('UW' || CHR(150),'US7ASCII')
FROM dual;
 
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;
SELECT utl_i18n.get_default_charset('English', 0)
FROM dual;

SELECT utl_i18n.get_default_charset('Japanese', 0)
FROM dual;
 
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;
SELECT utl_i18n.get_default_iso_currency('America')
FROM dual;

SELECT utl_i18n.get_default_iso_currency('Japan')
FROM dual;
 
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;
SELECT utl_i18n.get_default_linguistic_sort('German')
FROM dual;
 
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

DECLARE
 retval utl_i18n.string_array;
 cnt    PLS_INTEGER;
BEGIN
  retval := utl_i18n.get_local_languages('SWITZERLAND');
  dbms_output.put('Count = ');
  dbms_output.put_line(retval.LAST);
  cnt := retval.FIRST;

  WHILE cnt IS NOT NULL LOOP
    dbms_output.put_line(retval(cnt));
    cnt := retval.NEXT(cnt);
  END LOOP;
END;
/
 
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;
DECLARE
 retval utl_i18n.string_array;
 cnt    PLS_INTEGER;
BEGIN
  retval := utl_i18n.get_local_linguistic_sorts('American');
  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;
/

DECLARE
 retval utl_i18n.string_array;
 cnt    PLS_INTEGER;
BEGIN
  retval := utl_i18n.get_local_linguistic_sorts('Japanese');
  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_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

DECLARE
 retval utl_i18n.string_array;
 cnt    PLS_INTEGER;
BEGIN
  retval := utl_i18n.get_local_territories('ENGLISH');
  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_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

DECLARE
 retval utl_i18n.string_array;
 cnt    PLS_INTEGER;
BEGIN
  retval := utl_i18n.get_local_time_zones('AMERICA');
  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_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;

SELECT utl_i18n.get_translation('AMERICAN', 'ITALIAN')
FROM dual;

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

SELECT utl_i18n.get_translation('AMERICAN', 'GERMAN')
FROM dual;

SELECT utl_i18n.get_translation('AMERICAN', 'SWEDISH')
FROM dual;

SELECT utl_i18n.get_translation('AMERICAN', 'DANISH')
FROM dual;
 
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;
SELECT utl_i18n.map_charset('iso-8859-1', 0, 1)
FROM dual;

SELECT utl_i18n.map_charset('iso-8859-1', 1, 0)
FROM dual;
 
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;
SELECT utl_i18n.map_from_short_language('GB')
FROM dual;
 
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;
SELECT utl_i18n.map_language_from_iso('en_US')
FROM dual;
 
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;
SELECT utl_i18n.map_locale_to_iso('American', 'America')
FROM dual;
 
MAP_TERRITORY_FROM_ISO
Returns an Oracle territory name from an ISO locale utl_i18n.map_territory_from_iso(isolocale IN VARCHAR2) RETURN VARCHAR2;
SELECT utl_i18n.map_territory_from_iso('en_US')
FROM dual;
 
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;
SELECT utl_i18n.map_to_short_language('ENGLISH')
FROM dual;
 
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;
SELECT utl_i18n.raw_to_char('44616E204D6F7267616E','AL32UTF8')
FROM dual;
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;
TBD
 
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;
SELECT utl_i18n.raw_to_nchar('44616E204D6F7267616E','AL32UTF8')
FROM dual;
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;
TBD
 
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;
SELECT utl_i18n.string_to_raw('Dan Morgan','AL32UTF8')
FROM dual;
 
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;
SELECT utl_i18n.unescape_reference('UW&#x2013;')
FROM dual;
 
VALIDATE_SQLNAME

Validates an oracle object name		 utl_i18n.validate_sqlname(name VARCHAR2 CHARACTER SET ANY_CS) 
RETURN PLS_INTEGER;
set define off

SELECT utl_i18n.validate_sqlname('&')
FROM dual;
 
Related Topics
DBMS_CRYPTO