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 |
General
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 |
Used with shift_status. Must be set the first time it is called in piecewise conversion. |
PLS_INTEGER |
1 |
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' |
|
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 |
|
Security Model |
Execute is granted to PUBLIC with a PUBLIC synonym |
|
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–')
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; |