端末別OracleClientバージョンリスト - mieyanman @ ウィキ

• • テーブルの作成

CREATE TABLE MACHINE_VERSION

 (MACHINE VARCHAR2(64)
 ,OSUSER VARCHAR2(30)
 ,CLIENT_VERSION VARCHAR2(40)
 ,INSDATE DATE
 ,UPDDATE DATE
 ,CONSTRAINT PK_MACHINE_VERSION PRIMARY KEY (MACHINE,OSUSER,CLIENT_VERSION)
 )

;

• • 列コメントの設定

COMMENT ON COLUMN MACHINE_VERSION.MACHINE IS '端末名';
COMMENT ON COLUMN MACHINE_VERSION.OSUSER IS 'ユーザ名';
COMMENT ON COLUMN MACHINE_VERSION.CLIENT_VERSION IS 'バージョン';
COMMENT ON COLUMN MACHINE_VERSION.INSDATE IS '新規登録日時';
COMMENT ON COLUMN MACHINE_VERSION.UPDDATE IS '最終更新日時';

• • PUBLICシノニムの作成

CREATE PUBLIC SYNONYM SYN_MACHINE_VERSION FOR MACHINE_VERSION;

• • トリガの作成

CREATE OR REPLACE TRIGGER MACHINE_VERSION_TRG1

 BEFORE INSERT OR UPDATE ON MACHINE_VERSION FOR EACH ROW

BEGIN

   IF INSERTING THEN
       :NEW.INSDATE := SYSDATE;
       :NEW.UPDDATE := SYSDATE;
   ELSIF UPDATING THEN
       :NEW.UPDDATE := SYSDATE;
   END IF;

END;
/

• • オブジェクト権限の付与

GRANT SELECT,INSERT,UPDATE,DELETE ON MACHINE_VERSION TO PUBLIC;

• • 接続端末別OracleClientバージョン情報のマージ

MERGE INTO SYN_MACHINE_VERSION

 USING
   (
   SELECT UPPER(T1.MACHINE) AS 端末名
         ,UPPER(T1.OSUSER) AS ユーザ名
         ,UPPER(T2.CLIENT_VERSION) AS バージョン
     FROM GV$SESSION T1
       LEFT OUTER JOIN
         (
         SELECT INST_ID
               ,SID
               ,SERIAL#
               ,CLIENT_VERSION
           FROM GV$SESSION_CONNECT_INFO
           WHERE AUTHENTICATION_TYPE ='DATABASE'
             AND OSUSER <>'SYSTEM'
           GROUP BY INST_ID
                   ,SID
                   ,SERIAL#
                   ,OSUSER
                   ,CLIENT_VERSION
         ) T2
         ON  T1.INST_ID = T2.INST_ID
         AND T1.SID = T2.SID
         AND T1.SERIAL# = T2.SERIAL#
     WHERE T1.USERNAME IS NOT NULL
       AND T1.TYPE ='USER'
     GROUP BY T1.MACHINE
             ,T1.OSUSER
             ,T2.CLIENT_VERSION
   )
   ON (MACHINE = 端末名 AND OSUSER = ユーザ名 AND CLIENT_VERSION = バージョン)
 WHEN NOT MATCHED THEN
   INSERT(MACHINE,OSUSER,CLIENT_VERSION) VALUES(端末名,ユーザ名,バージョン)

;