sys_context Function Oracle and its usage

SYS_CONTEXT function returns set of defined values within the database(based out of parameters). This can be called from both SQL and PLSQL.

Example:-

SELECT SYS_CONTEXT (‘USERENV’, ‘NLS_DATE_FORMAT’)  FROM DUAL;

return NLS_DATE_FORMAT as DD-MON-RR ( In my session ). USERENV is the parameter

Another example below

 

Below few list of all SYS_CONTEXT parameters (most important )

Parameter	Description
CURRENT_SQL	First 4000 bytes of the current SQL.
CURRENT_SQL_LENGTH	Current SQL Length
DB_DOMAIN	DB Domain
DB_NAME	DB Name
HOST	Host Name
INSTANCE	Instance ID
INSTANCE_NAME	Instance Name
IP_ADDRESS	IP Address
ISDBA	Yes if logged as DBA
LANG	Language Country
LANGUAGE	Language Detail
MODULE	Tool Name
NETWORK_PROTOCOL	Eg:- TCP
NLS_CALENDAR	Eg:- Gregorian
NLS_CURRENCY	Currency
NLS_DATE_FORMAT	Date format
NLS_DATE_LANGUAGE	Date Language
NLS_SORT	Eg:- Binary
NLS_TERRITORY	NLS country
OS_USER	OS User Name
SERVER_HOST	Server Host
SERVICE_NAME	Service Name
SESSION_USER	Session User Name
SESSION_USERID	Session User ID
SESSIONID	Session Id
SID	Process ID
TERMINAL	Client Machine Name

For complete list please read Oracle Documentation

Usage

If I want to create audit table with information like userid, machine name, ip address, SYS_CONTEXT will help. See below sample

Create table emp_audit
(
emp_id number,
create_date timestamp default systimestamp,
created_by varchar2(50) default sys_context (‘userenv’, ‘session_user’),
ipaddress varchar2(50) default sys_context (‘userenv’,’IP_ADDRESS’),
module varchar2(50) default sys_context (‘userenv’,’MODULE’)
);

insert into emp_audit(emp_id) values (1000);

select * from emp_audit;

You can see SYS_CONTEXT used in another post Simple Debugging Tool