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
