Monthly Archives: July 2014

What is SYS_CONTEXT ??

SYS_CONTEXT is an Oracle function which can be used to retrieve useful information about the Oracle environment. This can be used both in SQL and PL/SQL.

WITH user_environment(param, param_value) AS
(
	select 'AUDITED_CURSORID', SYS_CONTEXT('USERENV','AUDITED_CURSORID') from dual UNION ALL
	select 'AUTHENTICATED_IDENTITY'		, SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') from dual UNION ALL
	select 'AUTHENTICATION_DATA'		, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') from dual UNION ALL
	select 'AUTHENTICATION_METHOD'		, SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') from dual UNION ALL
	select 'BG_JOB_ID'					, SYS_CONTEXT('USERENV','BG_JOB_ID') from dual UNION ALL
	select 'CLIENT_IDENTIFIER'			, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') from dual UNION ALL
	select 'CLIENT_INFO'				, SYS_CONTEXT('USERENV','CLIENT_INFO') from dual UNION ALL
	select 'CURRENT_BIND'				, SYS_CONTEXT('USERENV','CURRENT_BIND') from dual UNION ALL
	select 'CURRENT_EDITION_ID'			, SYS_CONTEXT('USERENV','CURRENT_EDITION_ID') from dual UNION ALL
	select 'CURRENT_EDITION_NAME'		, SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') from dual UNION ALL
	select 'CURRENT_SCHEMA'				, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') from dual UNION ALL
	select 'CURRENT_SCHEMAID'			, SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') from dual UNION ALL
	select 'CURRENT_SQL'				, SYS_CONTEXT('USERENV','CURRENT_SQL') from dual UNION ALL
	select 'CURRENT_SQLn'				, SYS_CONTEXT('USERENV','CURRENT_SQLn') from dual UNION ALL
	select 'CURRENT_SQL_LENGTH'			, SYS_CONTEXT('USERENV','CURRENT_SQL_LENGTH') from dual UNION ALL
	select 'CURRENT_USER'				, SYS_CONTEXT('USERENV','CURRENT_USER') from dual UNION ALL
	select 'CURRENT_USERID'				, SYS_CONTEXT('USERENV','CURRENT_USERID') from dual UNION ALL
	select 'DATABASE_ROLE'				, SYS_CONTEXT('USERENV','DATABASE_ROLE') from dual UNION ALL
	select 'DB_DOMAIN'					, SYS_CONTEXT('USERENV','DB_DOMAIN') from dual UNION ALL
	select 'DB_NAME'					, SYS_CONTEXT('USERENV','DB_NAME') from dual UNION ALL
	select 'DB_UNIQUE_NAME'				, SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') from dual UNION ALL
	select 'DBLINK_INFO'				, SYS_CONTEXT('USERENV','DBLINK_INFO') from dual UNION ALL
	select 'ENTRYID'					, SYS_CONTEXT('USERENV','ENTRYID') from dual UNION ALL
	select 'ENTERPRISE_IDENTITY'		, SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY') from dual UNION ALL
	select 'FG_JOB_ID'					, SYS_CONTEXT('USERENV','FG_JOB_ID') from dual UNION ALL
	select 'GLOBAL_CONTEXT_MEMORY'		, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') from dual UNION ALL
	select 'GLOBAL_UID'					, SYS_CONTEXT('USERENV','GLOBAL_UID') from dual UNION ALL
	select 'HOST'						, SYS_CONTEXT('USERENV','HOST') from dual UNION ALL
	select 'IDENTIFICATION_TYPE'		, SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE') from dual UNION ALL
	select 'INSTANCE'					, SYS_CONTEXT('USERENV','INSTANCE') from dual UNION ALL
	select 'INSTANCE_NAME'				, SYS_CONTEXT('USERENV','INSTANCE_NAME') from dual UNION ALL
	select 'IP_ADDRESS'					, SYS_CONTEXT('USERENV','IP_ADDRESS') from dual UNION ALL
	select 'ISDBA'						, SYS_CONTEXT('USERENV','ISDBA') from dual UNION ALL
	select 'LANG'						, SYS_CONTEXT('USERENV','LANG') from dual UNION ALL
	select 'LANGUAGE'					, SYS_CONTEXT('USERENV','LANGUAGE') from dual UNION ALL
	select 'MODULE'						, SYS_CONTEXT('USERENV','MODULE') from dual UNION ALL
	select 'NETWORK_PROTOCOL'			, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') from dual UNION ALL
	select 'NLS_CALENDAR'				, SYS_CONTEXT('USERENV','NLS_CALENDAR') from dual UNION ALL
	select 'NLS_CURRENCY'				, SYS_CONTEXT('USERENV','NLS_CURRENCY') from dual UNION ALL
	select 'NLS_DATE_FORMAT'			, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') from dual UNION ALL
	select 'NLS_DATE_LANGUAGE'			, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') from dual UNION ALL
	select 'NLS_SORT'					, SYS_CONTEXT('USERENV','NLS_SORT') from dual UNION ALL
	select 'NLS_TERRITORY'				, SYS_CONTEXT('USERENV','NLS_TERRITORY') from dual UNION ALL
	select 'OS_USER'					, SYS_CONTEXT('USERENV','OS_USER') from dual UNION ALL
	select 'POLICY_INVOKER'				, SYS_CONTEXT('USERENV','POLICY_INVOKER') from dual UNION ALL
	select 'PROXY_ENTERPRISE_IDENTITY'	, SYS_CONTEXT('USERENV','PROXY_ENTERPRISE_IDENTITY') from dual UNION ALL
	select 'PROXY_USER'					, SYS_CONTEXT('USERENV','PROXY_USER') from dual UNION ALL
	select 'PROXY_USERID'				, SYS_CONTEXT('USERENV','PROXY_USERID') from dual UNION ALL
	select 'SERVER_HOST'				, SYS_CONTEXT('USERENV','SERVER_HOST') from dual UNION ALL
	select 'SERVER_NAME'				, SYS_CONTEXT('USERENV','SERVICE_NAME') from dual UNION ALL
	select 'SESSION_EDITION_ID'			, SYS_CONTEXT('USERENV','SESSION_EDITION_ID') from dual UNION ALL
	select 'SESSION_EDITION_NAME'		, SYS_CONTEXT('USERENV','SESSION_EDITION_NAME') from dual UNION ALL
	select 'SESSION_USER'				, SYS_CONTEXT('USERENV','SESSION_USER') from dual UNION ALL
	select 'SESSION_USERID'				, SYS_CONTEXT('USERENV','SESSION_USERID') from dual UNION ALL
	select 'SESSIONID'					, SYS_CONTEXT('USERENV','SESSIONID') from dual UNION ALL
	select 'SID'						, SYS_CONTEXT('USERENV','SID') from dual UNION ALL
	select 'STATEMENTID'				, SYS_CONTEXT('USERENV','STATEMENTID') from dual UNION ALL
	select 'TERMINAL'					, SYS_CONTEXT('USERENV','TERMINAL') from dual
)
---------
SELECT 
   param, 
   param_value
FROM user_environment;
Advertisements