Oracle UTL_I18N
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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'
ARABIC_LATIN_ISO233 VARCHAR2(30) 'arabic_latin_iso233'
CYRILLIC_LATIN_ISO9 VARCHAR2(30) 'cyrillic_latin_iso9'
GREEK_LATIN_ISO843 VARCHAR2(30) 'greek_latin_iso843'
HEBREW_LATIN_ISO259 VARCHAR2(30) 'hebrew_latin_iso259'
CYR_ASCII_ICAO9303 VARCHAR2(30) 'cyr_ascii_icao9303'
CYR_BG_ASCII_ICAO9303 VARCHAR2(30) 'cyr_bg_ascii_icao9303'
CYR_BY_ASCII_ICAO9303 VARCHAR2(30) 'cyr_by_ascii_icao9303'
CYR_MK_ASCII_ICAO9303 VARCHAR2(30) 'cyr_mk_ascii_icao9303'
CYR_RS_ASCII_ICAO9303 VARCHAR2(30) 'cyr_rs_ascii_icao9303'
CYR_UA_ASCII_ICAO9303 VARCHAR2(30) 'cyr_ua_ascii_icao9303'
LATIN_ASCII_DIN91379 VARCHAR2(30) 'latin_ascii_din91379'
MODERN_HEBREW_LATIN_ISO259_2 VARCHAR2(30) 'modern_hebrew_latin_iso259_2'
Data Types TYPE string_array IS TABLE of VARCHAR2(32767)
INDEX BY BINARY_INTEGER;

TYPE language_charset_result_record IS RECORD(
language VARCHAR2(28),
charset  VARCHAR2(28),
score    NUMBER);

TYPE language_charset_result_table IS TABLE OF language_charset_result_record;

/* language detection result record and table */
TYPE language_result_record IS RECORD(
language VARCHAR2(28),
score    NUMBER);

TYPE language_result_table IS TABLE OF language_result_record;

/* character set detection result record and table */
TYPE charset_result_record IS RECORD(
charset VARCHAR2(28),
score   NUMBER);

TYPE charset_result_table IS TABLE OF charset_result_record;
Dependencies
DBMS_BLOCKCHAIN_TABLE DBMS_SCHEDULER SPARQL_SERVICE_IMPL_T
DBMS_CUBE DBMS_VECTOR UTL_I18_LIB
DBMS_ISCHED DBMS_VECTOR_CHAIN UTL_RAW
DBMS_ISCHED_REMOTE_ACCESS HTF UTL_SMTP
DBMS_REPORT PLITBLM  
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
Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utli18n.sql
Subprograms
DETECT_CHARSET GET_LOCAL_LINGUISTIC_SORTS MAP_TERRITORY_FROM_ISO
DETECT_LANGIAGE GET_LOCAL_TERRITORIES MAP_TO_SHORT_LANGUAGE
DETECT_LANGIAGE_CHARSET GET_LOCAL_TIME_ZONES RAW_TO_CHAR
ENCODE_SQL_XML GET_MAX_CHARACTER_SIZE RAW_TO_NCHAR
ESCAPE_REFERENCE GET_TRANSLATION STRING_TO_RAW
GET_COMMON_TIME_ZONES MAP_CHARSET TRANSLITERATE
GET_DEFAULT_CHARSET MAP_FROM_SHORT_LANGUAGE UNESCAPE_REFERENCE
GET_DEFAULT_ISO_CURRENCY MAP_LANGUAGE_FROM_ISO VALIDATE_CHARACTER_ENCODING
GET_DEFAULT_LINGUISTIC_SORT MAP_LOCALE_TO_ISO VALIDATE_SQLNAME
GET_LOCAL_LANGUAGES    
 
DETECT_CHARSET (new 23ai)
Detects a character set of a BLOB

Overload 1		 utl_i18n.detect_charset(
result      OUT NOCOPY sys.utl_i18n.charset_result_table,
src         IN         BLOB,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a character set of a CLOB

Overload 2		 utl_i18n.detect_charset(
result      OUT NOCOPY sys.utl_i18n.charset_result_table,
src         IN         CLOB,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a character set of a BFILE

Overload 3		 utl_i18n.detect_charset(
result      OUT NOCOPY sys.utl_i18n.charset_result_table,
src         IN         BFILE,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a character set of a VARCHAR2 or NVARCHAR2

Overload 4		 utl_i18n.detect_charset(
result      OUT NOCOPY sys.utl_i18n.charset_result_table,
src         IN         VARCHAR2,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
 
DETECT_LANGUAGE (new 23ai)
Detects a language in a BLOB

Overload 1		 utl_i18n.detect_language(
result      OUT NOCOPY sys.utl_i18n.language_result_table,
src         IN         BLOB,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a language in a CLOB

Overload 2		 utl_i18n.detect_language(
result      OUT NOCOPY sys.utl_i18n.language_result_table,
src         IN         CLOB,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a language in a BFILE

Overload 3		 utl_i18n.detect_language(
result      OUT NOCOPY sys.utl_i18n.language_result_table,
src         IN         BFILE,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a language in a VARCHAR2 or NVARCHAR2

Overload 4		 utl_i18n.detect_language(
result      OUT NOCOPY sys.utl_i18n.language_result_table,
src         IN         VARCHAR2,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
 
DETECT_LANGUAGE_CHARSET (new 23ai)
Detects a language and character set pair of a BLOB

Overload 1		 utl_i18n.detect_language_charset(
result      OUT NOCOPY sys.utl_i18n.language_charset_result_table,
src         IN         BLOB,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a language and character set pair of a CLOB

Overload 2		 utl_i18n.detect_language_charset(
result      OUT NOCOPY sys.utl_i18n.language_charset_result_table,
src         IN         CLOB,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a language and character set pair of a BFILE

Overload 3		 utl_i18n.detect_language_charset(
result      OUT NOCOPY sys.utl_i18n.language_charset_result_table,
src         IN         BFILE,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
Detects a language and character set pair of a VARCHAR2 or NVARCHAR2

Overload 4		 utl_i18n.detect_language_charset(
result      OUT NOCOPY sys.utl_i18n.language_charset_result_table,
src         IN         VARCHAR2,
profile     IN         BINARY_INTEGER DEFAULT 1,
num_results IN         BINARY_INTEGER DEFAULT NULL,
sample_size IN         BINARY_INTEGER DEFAULT NULL);
TBD
 
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);

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

SELECT utl_i18n.encode_sql_xml('_xHHHHHHHH_',1);

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;
SELECT utl_i18n.escape_reference('UW' || CHR(150),'US7ASCII');

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;
SELECT utl_i18n.get_default_charset('English', 0);

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

SELECT utl_i18n.get_default_charset('Japanese', 0);

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;
SELECT utl_i18n.get_default_iso_currency('America');

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

SELECT utl_i18n.get_default_iso_currency('Japan');

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

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;
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;
/
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;
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;
/
Count = 2
GENERIC_M
BINARY

PL/SQL procedure successfully completed.

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;
/
Count = 5
SCHINESE_PINYIN_M
SCHINESE_STROKE_M
SCHINESE_RADICAL_M
GBK
BINARY

PL/SQL procedure successfully completed.
 
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;
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;
/
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;
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;
/
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;
SELECT utl_i18n.get_max_character_size('US7ASCII');

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

SELECT utl_i18n.get_max_character_size('JA16EUC');

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

SELECT utl_i18n.get_max_character_size('AL32UTF8');

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;
SQL> set linesize 121
SQL> col parameter format a20
SQL> col value format a20

SELECT * FROM gv$nls_valid_values;

SELECT utl_i18n.get_translation('SIMPLIFIED CHINESE', 'ITALIAN');

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

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

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

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

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

SELECT utl_i18n.get_translation('SIMPLIFIED CHINESE', 'SWEDISH');

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

SELECT utl_i18n.get_translation('AMERICAN', 'DANISH');

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

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

SELECT utl_i18n.map_charset('iso-8859-1', 1, 0);

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;
SELECT utl_i18n.map_from_short_language('GB');

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

SELECT utl_i18n.map_from_short_language('SW');

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;
SELECT utl_i18n.map_language_from_iso('en_US');

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;
SELECT utl_i18n.map_locale_to_iso('American', 'America');

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;
SELECT utl_i18n.map_territory_from_iso('en_US');

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;
SELECT utl_i18n.map_to_short_language('ENGLISH');

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;
SELECT utl_i18n.raw_to_char('44616E204D6F7267616E','AL32UTF8');

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;
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;
/
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;
SELECT utl_i18n.raw_to_nchar('44616E204D6F7267616E','AL32UTF8');

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

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;
/
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;
SELECT utl_i18n.string_to_raw('Dan Morgan','AL32UTF8');

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;
SELECT utl_i18n.unescape_reference('UW–');

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;
SELECT utl_i18n.validate_character_encoding('Daniel Morgan');

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;
DECLARE
 inClob CLOB := 'Morgan''s Library';
 retVal NUMBER;
BEGIN
  retVal := utl_i18n.validate_character_encoding(inClob);
  dbms_output.put_line(retVal);
END;
/
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
SELECT utl_i18n.validate_sqlname('&');

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 26ai

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