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

SYS_CONTEXT
SYS_CONTEXT

 

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;

SYS_CONTEXT Example
SYS_CONTEXT Example

You can see SYS_CONTEXT used in another post Simple Debugging Tool

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s