Oracle UTL_I18N
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_CUBE PLITBLM UTL_RAW
DBMS_ISCHED UTL_I18_LIB  
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;
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('Simplified Chinese');
  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_MAX_CHARACTER_SIZE (new 12.1)
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;
SELECT utl_i18n.get_max_character_size('US7ASCII')
FROM dual;

SELECT utl_i18n.get_max_character_size('JA16EUC')
FROM dual;

SELECT utl_i18n.get_max_character_size('AL32UTF8')
FROM dual;
 
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('SIMPLIFIED CHINESE', 'ITALIAN')
FROM dual;

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

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

SELECT utl_i18n.get_translation('SIMPLIFIED CHINESE', '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;
set serveroutput on

DECLARE
 scnlen PLS_INTEGER;
 shstat PLS_INTEGER := 1;
 retval VARCHAR2(30);
BEGIN
  retval := utl_i18n.raw_to_char('44616E204D6F7267616E', 'AL32UTF8', scnlen, shstat);
  dbms_output.put_line(retval);
  dbms_output.put_line(scnlen);
  dbms_output.put_line(shstat);
END;
/
 
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;
set serveroutput on

DECLARE
 scnlen PLS_INTEGER;
 shstat PLS_INTEGER := 3;
 retval VARCHAR2(30);
BEGIN
  retval := utl_i18n.raw_to_nchar('44616E204D6F7267616E', 'AL32UTF8', scnlen, shstat);
  dbms_output.put_line(retval);
  dbms_output.put_line(scnlen);
  dbms_output.put_line(shstat);
END;
/
 
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–')
FROM dual;
 
VALIDATE_SQLNAME
Validates an oracle object name utl_i18n.validate_sqlname(name IN VARCHAR2 CHARACTER SET ANY_CS) RETURN PLS_INTEGER;
set define off

SELECT utl_i18n.validate_sqlname('&')
FROM dual;

Related Topics
Character Sets
Character Set Functions
DBMS_CRYPTO
Packages
UTL_GDK

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-2014 Daniel A. Morgan All Rights Reserved