使い方は パッケージ変数を参照するだけ （パッケージの読み込み時に自動的に初期化される）
info プロシージャは値一覧表示、reload プロシージャは再読み込みする。
SQL> set serveroutput on
SQL> execute env.info
LAST_DATE 2008/01/08 12:23:57
VERSION = 10.2.0.3.0
COMPATIBLE = 10.2.0.3.0
DB_RELEASE = 10.2
OS = MICROSOFT WINDOWS IA (32-BIT)
ENDIAN = LITTLE
IS_WINDOWS = TRUE
NEWLINE = CR+LF
PORT_STRING = IBMPC/WIN_NT-8.1.0
IP = 192.168.x.x
HOSTNAME = RIVUS
CHAR_CS = JA16SJISTILDE
NCHAR_CS = AL16UTF16
NLS_CALENDAR = GREGORIAN
NLS_CHARACTERSET = JA16SJISTILDE
NLS_COMP = BINARY
NLS_CURRENCY = \
NLS_DATE_FORMAT = YYYY/MM/DD HH24:MI:SS
NLS_DATE_LANGUAGE = JAPANESE
NLS_DUAL_CURRENCY = \
NLS_ISO_CURRENCY = JAPAN
NLS_LANGUAGE = JAPANESE
NLS_LENGTH_SEMANTICS = BYTE
NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_NCHAR_CONV_EXCP = FALSE
NLS_NUMERIC_CHARACTERS = .,
NLS_SORT = BINARY
NLS_TERRITORY = JAPAN
NLS_TIMESTAMP_FORMAT = RR-MM-DD HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT = RR-MM-DD HH24:MI:SSXFF TZR
NLS_TIME_FORMAT = HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT = HH24:MI:SSXFF TZR
PL/SQLプロシージャが正常に完了しました。
ENV パッケージ ソースコード
CREATE OR REPLACE PACKAGE RIVUS.ENV
AUTHID CURRENT_USER
IS
--Assosiative Array Index by VARCHAR2( Oracle 9i R2 )
TYPE tNLS_PARAMETERS IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
LAST_DATE DATE;
VERSION VARCHAR2(30); -- eg. 10.2.0.1.0
COMPATIBLE VARCHAR2(30); -- 10.2.0.1.0
DB_RELEASE NUMBER(4,2); -- 10.2
OS VARCHAR2(101); -- Microsoft Windows IA (32-bit)
ENDIAN VARCHAR2(8); -- LITTLE
PORT_STRING VARCHAR2(100); -- IBMPC/WIN_NT-8.1.0
IS_WINDOWS BOOLEAN; -- TRUE or FALSE
NEWLINE VARCHAR2(8); -- CR+LF(Windows) or LF / binary char
IP VARCHAR2(30); -- 192.168.0.1
HOSTNAME VARCHAR2(64); -- hostname
CHAR_CS VARCHAR2(30); -- JA16SJIS
NCHAR_CS VARCHAR2(30); -- AL16UTF16
NLS_PARAMETERS tNLS_PARAMETERS;
PROCEDURE INFO;
PROCEDURE RELOAD;
END;
/
CREATE OR REPLACE PACKAGE BODY RIVUS.ENV
IS
-- constant
PLATFORM_SQL CONSTANT VARCHAR2(2000) :=
'select d.platform_name, t.endian_format
from sys.v_$database d, sys.v_$transportable_platform t
where d.platform_id = t.platform_id';
PLATFORM_USAGE CONSTANT VARCHAR2(2000) :=
'grant select on SYS.V_$DATABASE to <username>';
-- exception
eCANT_ACCESS EXCEPTION;
PRAGMA EXCEPTION_INIT(eCANT_ACCESS, -903);
-- private programs
PROCEDURE PUT_LINE(P_MSG VARCHAR2);
--
PROCEDURE SET_PROPERTY_PLATFORM(P_OS OUT VARCHAR2,
P_ENDIAN OUT VARCHAR2,
P_PORT OUT VARCHAR2)
IS
vPlatform VARCHAR2(101);
vEndian VARCHAR2(14);
BEGIN
IF (DB_RELEASE >= 10) THEN
BEGIN
EXECUTE IMMEDIATE PLATFORM_SQL INTO vPlatform, vEndian;
P_OS := UPPER(vPlatform);
P_ENDIAN := UPPER(vEndian);
EXCEPTION
WHEN eCANT_ACCESS THEN
PUT_LINE(PLATFORM_USAGE);
END;
END IF;
P_PORT := UPPER(DBMS_UTILITY.PORT_STRING);
END SET_PROPERTY_PLATFORM;
--
PROCEDURE SET_PROPERTY_WINDOWS(P_IS_WINDOWS OUT BOOLEAN, P_NEWLINE OUT VARCHAR2)
IS
BEGIN
IF (UPPER(PORT_STRING) LIKE '%/WIN_%') THEN
P_IS_WINDOWS := TRUE;
P_NEWLINE := CHR(13) || CHR(10);
ELSE
P_IS_WINDOWS := FALSE;
P_NEWLINE := CHR(10);
END IF;
END SET_PROPERTY_WINDOWS;
--
PROCEDURE SET_PROPERTY_DB_RELEASE(P_DBRELEASE OUT NUMBER)
IS
BEGIN
P_DBRELEASE := SUBSTR(VERSION, 1, INSTRB(VERSION, '.', 1, 2)- 1);
END SET_PROPERTY_DB_RELEASE;
--
PROCEDURE SET_PROPERTY_NETWORK(P_IP OUT VARCHAR2, P_HOSTNAME OUT VARCHAR2)
IS
BEGIN
P_IP := UTL_INADDR.GET_HOST_ADDRESS(NULL);
P_HOSTNAME := UTL_INADDR.GET_HOST_NAME(NULL);
EXCEPTION
WHEN UTL_INADDR.UNKNOWN_HOST THEN
NULL;
END;
-- Oracle 9i R2
PROCEDURE SET_PROPERTY_NLS(P_NLS_PARAMS OUT NOCOPY tNLS_PARAMETERS)
IS
BEGIN
FOR vRec IN (select parameter, value from sys.v_$nls_parameters)
LOOP
P_NLS_PARAMS(UPPER(vRec.PARAMETER)) := vRec.VALUE;
END LOOP;
END;
--
PROCEDURE INITIALIZE
IS
BEGIN
LAST_DATE := SYSDATE;
DBMS_UTILITY.DB_VERSION(VERSION, COMPATIBLE);
SET_PROPERTY_DB_RELEASE(DB_RELEASE);
SET_PROPERTY_PLATFORM(OS, ENDIAN, PORT_STRING);
SET_PROPERTY_WINDOWS(IS_WINDOWS, NEWLINE);
SET_PROPERTY_NLS(NLS_PARAMETERS);
SET_PROPERTY_NETWORK(IP, HOSTNAME);
CHAR_CS := UPPER(NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS')));
NCHAR_CS := UPPER(NLS_CHARSET_NAME(NLS_CHARSET_ID('NCHAR_CS')));
END INITIALIZE;
-- for debugging
PROCEDURE PUT_LINE(P_MSG VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(P_MSG);
END PUT_LINE;
--
PROCEDURE PUT_LINE(P_ARRAY tNLS_PARAMETERS)
IS
vIndexName VARCHAR2(64);
BEGIN
vIndexName := P_ARRAY.FIRST;
LOOP
EXIT WHEN vIndexName IS NULL;
PUT_LINE(vIndexName || ' = ' || P_ARRAY(vIndexName));
vIndexName := P_ARRAY.NEXT(vIndexName);
END LOOP;
END;
---- global programs--
PROCEDURE INFO
IS
BEGIN
PUT_LINE('LAST_DATE ' || LAST_DATE);
PUT_LINE('VERSION = ' || VERSION);
PUT_LINE('COMPATIBLE = ' || COMPATIBLE);
PUT_LINE('DB_RELEASE = ' || DB_RELEASE);
PUT_LINE('OS = ' || OS);
PUT_LINE('ENDIAN = ' || ENDIAN);
PUT_LINE('IS_WINDOWS = '||CASE WHEN IS_WINDOWS = TRUE THEN 'TRUE' ELSE 'FALSE' END);
PUT_LINE('NEWLINE = ' || CASE WHEN LENGTH(NEWLINE) = 1 THEN 'LF' ELSE 'CR+LF' END);
PUT_LINE('PORT_STRING = ' || PORT_STRING);
PUT_LINE('IP = ' || IP);
PUT_LINE('HOSTNAME = ' || HOSTNAME);
PUT_LINE('CHAR_CS = ' || CHAR_CS);
PUT_LINE('NCHAR_CS = ' || NCHAR_CS);
PUT_LINE(NLS_PARAMETERS);
END INFO;
--
PROCEDURE RELOAD
IS
BEGIN
INITIALIZE;
END RELOAD;
--
BEGIN
INITIALIZE;
END;
/
などの最近？？のコンテキストデータは用意されていない）