This oddly named package is based on internationalization. Keeping
the leading I and trailing N, and removing what is in-between, 18
characters are eliminated. Internationalization is then abbreviated
as I18N, a small example of Oracle humor.
What is the package used for? For the most part, it is used to perform
translations or lookups based on the developer’s locale or input. For
example, one can list out time zones.
DECLARE
x
UTL_HTTP.HTML_PIECES;
len PLS_INTEGER;
BEGIN
x :=
UTL_HTTP.REQUEST_PIECES('http://www.oracle.com/', 100);
DBMS_OUTPUT.PUT_LINE(x.count || '
pieces were retrieved.');
DBMS_OUTPUT.PUT_LINE('with total
length ');
IF x.count < 1 THEN
DBMS_OUTPUT.PUT_LINE('0');
ELSE
len := 0;
FOR i in 1..x.count LOOP
len := len + length(x(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE(len);
END IF;
END;
/
Count = 119
Pacific/Pago_Pago
Pacific/Honolulu
America/Anchorage
America/Vancouver
America/Los_Angeles
America/Tijuana
America/Edmonton
…continues…
How about all of the local time zones based on America?
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.
Experiment with the subprograms and see what gets returned.
UTL_INADDR
This simple package can be used to get the host address and host name,
both remote and local. An example of the subprograms shows the
following with respect to a local host.
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);
-- get local host name
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);
-- get local IP addr
END;
/
T42
10.10.10.10
PL/SQL procedure successfully completed.
The name of the computer running this code is T42, and the IP address,
at least one of them, is 10.10.10.10, which may be recognized as a
dummy address used for the Microsoft loopback adapter.
What is the host name where dba-oracle.com is hosted? The nslookup
command returns an IP address of 65.109.93.192. Use this IP address
and see what the host name is.
select
UTL_INADDR.GET_HOST_NAME
('65.109.93.192')
from
dual;
UTL_INADDR.GET_HOST_NAME('65.109.93.192')
-----------------------------------------
dba-oracle.com
One of Google’s IP addresses is used in the following query just to
show that the hostname is not the obvious name as in the previous
example.
select
UTL_INADDR.GET_HOST_NAME
('64.233.167.99')
from
dual;
UTL_INADDR.GET_HOST_NAME('64.233.167.99')
-----------------------------------------
py-in-f99.google.com
UTL_LMS
The UTL_LMS package is handy when it comes to translating Oracle
messages into another language. People who moderate on an
Oracle-related forum have undoubtedly run across someone who posts a
topic and left the error message in the poster’s native tongue, which
invariably is not English.
With the right adjustment of the language setting, error messages can
be translated.
DECLARE
s varchar2(200);
i pls_integer;
BEGIN
i:=utl_lms.get_message(601, 'rdbms', 'oci',
'french', s);
dbms_output.put_line('OCI--00601 is:
'||s);
END;
/
OCI—00601 is: Echec du processus de nettoyage.
The input parameters are
errnum, product, facility, language and
message.
