学习致用——使用PL/SQL提取Oracle数据库上下文信息（用户环境） (Extraction of Oracle context/User environment)

学了这么久PL/SQL，是时候用一用了。

第一个任务：批量输出Oracle数据库上下文信息（用户环境）

代码：

DECLARE
   TERMINAL              VARCHAR2 (50);
   LANGUAG               VARCHAR2 (50);
   SESSIONID             VARCHAR2 (50);
   INSTANC               VARCHAR2 (50);
   ENTRYID               VARCHAR2 (50);
   ISDBA                 VARCHAR2 (50);
   NLS_TERRITORY         VARCHAR2 (50);
   NLS_CURRENC           VARCHAR2 (50);
   NLS_CALENDAR          VARCHAR2 (50);
   NLS_DATE_FORMAT       VARCHAR2 (50);
   NLS_DATE_LANGUAGE     VARCHAR2 (50);
   NLS_SORT              VARCHAR2 (50);
   CURRENT_USE           VARCHAR2 (50);
   CURRENT_USERID        VARCHAR2 (50);
   SESSION_USER          VARCHAR2 (50);
   SESSION_USERID        VARCHAR2 (50);
   PROXY_USER            VARCHAR2 (50);
   PROXY_USERID          VARCHAR2 (50);
   DB_DOMAIN             VARCHAR2 (50);
   DB_NAME               VARCHAR2 (50);
   HOST                  VARCHAR2 (50);
   OS_USER               VARCHAR2 (50);
   EXTERNAL_NAME         VARCHAR2 (50);
   IP_ADDRESS            VARCHAR2 (50);
   NETWORK_PROTOCOL      VARCHAR2 (50);
   BG_JOB_ID             VARCHAR2 (50);
   FG_JOB_ID             VARCHAR2 (50);
   AUTHENTICATION_TYPE   VARCHAR2 (50);
   AUTHENTICATION_DATA   VARCHAR2 (50);
BEGIN
   SELECT SYS_CONTEXT ('USERENV', 'TERMINAL') AS TERMINAL
     INTO TERMINAL
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'LANGUAGE') AS LANGUAG
     INTO LANGUAG
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'SESSIONID') AS SESSIONID
     INTO SESSIONID
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'INSTANCE') AS INSTANC
     INTO INSTANC
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'ENTRYID') AS ENTRYID
     INTO ENTRYID
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'ISDBA') AS ISDBA INTO ISDBA FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'NLS_TERRITORY') AS NLS_TERRITORY
     INTO NLS_TERRITORY
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'NLS_CURRENCY') AS NLS_CURRENC
     INTO NLS_CURRENC
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'NLS_CALENDAR') AS NLS_CALENDAR
     INTO NLS_CALENDAR
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'NLS_DATE_FORMAT') AS NLS_DATE_FORMAT
     INTO NLS_DATE_FORMAT
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE') AS NLS_DATE_LANGUAGE
     INTO NLS_DATE_LANGUAGE
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'NLS_SORT') AS NLS_SORT
     INTO NLS_SORT
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'CURRENT_USER') AS CURRENT_USE
     INTO CURRENT_USE
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'CURRENT_USERID') AS CURRENT_USERID
     INTO CURRENT_USERID
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') AS SESSION_USER
     INTO SESSION_USER
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'SESSION_USERID') AS SESSION_USERID
     INTO SESSION_USERID
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'PROXY_USER') AS PROXY_USER
     INTO PROXY_USER
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'PROXY_USERID') AS PROXY_USERID
     INTO PROXY_USERID
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'DB_DOMAIN') AS DB_DOMAIN
     INTO DB_DOMAIN
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'DB_NAME') AS DB_NAME
     INTO DB_NAME
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'HOST') AS HOST INTO HOST FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'OS_USER') AS OS_USER
     INTO OS_USER
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'EXTERNAL_NAME') AS EXTERNAL_NAME
     INTO EXTERNAL_NAME
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') AS IP_ADDRESS
     INTO IP_ADDRESS
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL') AS NETWORK_PROTOCOL
     INTO NETWORK_PROTOCOL
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'BG_JOB_ID') AS BG_JOB_ID
     INTO BG_JOB_ID
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'FG_JOB_ID') AS FG_JOB_ID
     INTO FG_JOB_ID
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATION_TYPE')
             AS AUTHENTICATION_TYPE
     INTO AUTHENTICATION_TYPE
     FROM DUAL;

   SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATION_DATA')
             AS AUTHENTICATION_DATA
     INTO AUTHENTICATION_DATA
     FROM DUAL;

   DBMS_OUTPUT.put_line ('TERMINAL:' || TERMINAL);
   DBMS_OUTPUT.put_line ('LANGUAG:' || LANGUAG);
   DBMS_OUTPUT.put_line ('SESSIONID:' || SESSIONID);
   DBMS_OUTPUT.put_line ('INSTANC:' || INSTANC);
   DBMS_OUTPUT.put_line ('ENTRYID:' || ENTRYID);
   DBMS_OUTPUT.put_line ('ISDBA:' || ISDBA);
   DBMS_OUTPUT.put_line ('NLS_TERRITORY:' || NLS_TERRITORY);
   DBMS_OUTPUT.put_line ('NLS_CURRENC:' || NLS_CURRENC);
   DBMS_OUTPUT.put_line ('NLS_CALENDAR:' || NLS_CALENDAR);
   DBMS_OUTPUT.put_line ('NLS_DATE_FORMAT:' || NLS_DATE_FORMAT);
   DBMS_OUTPUT.put_line ('NLS_DATE_LANGUAGE:' || NLS_DATE_LANGUAGE);
   DBMS_OUTPUT.put_line ('NLS_SORT:' || NLS_SORT);
   DBMS_OUTPUT.put_line ('CURRENT_USE:' || CURRENT_USE);
   DBMS_OUTPUT.put_line ('CURRENT_USERID:' || CURRENT_USERID);
   DBMS_OUTPUT.put_line ('SESSION_USER:' || SESSION_USER);
   DBMS_OUTPUT.put_line ('SESSION_USERID:' || SESSION_USERID);
   DBMS_OUTPUT.put_line ('PROXY_USER:' || PROXY_USER);
   DBMS_OUTPUT.put_line ('PROXY_USERID:' || PROXY_USERID);
   DBMS_OUTPUT.put_line ('DB_DOMAIN:' || DB_DOMAIN);
   DBMS_OUTPUT.put_line ('DB_NAME:' || DB_NAME);
   DBMS_OUTPUT.put_line ('HOST:' || HOST);
   DBMS_OUTPUT.put_line ('OS_USER:' || OS_USER);
   DBMS_OUTPUT.put_line ('EXTERNAL_NAME:' || EXTERNAL_NAME);
   DBMS_OUTPUT.put_line ('IP_ADDRESS:' || IP_ADDRESS);
   DBMS_OUTPUT.put_line ('NETWORK_PROTOCOL:' || NETWORK_PROTOCOL);
   DBMS_OUTPUT.put_line ('BG_JOB_ID:' || BG_JOB_ID);
   DBMS_OUTPUT.put_line ('FG_JOB_ID:' || FG_JOB_ID);
   DBMS_OUTPUT.put_line ('AUTHENTICATION_TYPE:' || AUTHENTICATION_TYPE);
   DBMS_OUTPUT.put_line ('AUTHENTICATION_DATA:' || AUTHENTICATION_DATA);
END;
/

整理后的资料（环境变量值做了masking处理，仅作示例参考！）：

User Envirionment	Sample_Value
TERMINAL	ComputerName
LANGUAG	NLS_LANG
SESSIONID	123456789
INSTANC	INSTANCE
ENTRYID	ENTRY ID
ISDBA	FALSE
NLS_TERRITORY	AMERICA
NLS_CURRENC	$
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD-MON-RR
NLS_DATE_LANGUAGE	AMERICAN
NLS_SORT	BINARY
CURRENT_USE	DBUserName
CURRENT_USERID	123
SESSION_USER	DBUserName
SESSION_USERID	123
PROXY_USER
PROXY_USERID
DB_DOMAIN	iCompany.com
DB_NAME	orcl520
HOST	APAC\computerName
OS_USER	OperatingSystemUserName
EXTERNAL_NAME
IP_ADDRESS	10.123.456.789
NETWORK_PROTOCOL	tcp
BG_JOB_ID
FG_JOB_ID	0
AUTHENTICATION_TYPE	DATABASE
AUTHENTICATION_DATA