I dedicated to SYS_CONTEXT function one extra article even though there is nothing much to say 🙂 SYS_CONTEXT is a function used to retrieve information about Oracle environment.
There are three parameters
- namespace
- most often USERENV
- to see them all -> SELECT namespace FROM dba_context;
- parameter
- a parameter for retrieval
- length – optional
- you can limit the return value length by this parameter
- if omitted or invalid – default (256 bytes) is used
Usage
You might ask why would you need to use this or how? The answer is very simple. For logging/auditing, checking, automating or restricting purposes. Let’s imagine a situation where you want only a particular user can run a certain part of code in your procedure. To assure that, you can restrict that part by checking who is running it.
CREATE OR REPLACE PROCEDURE my_proc IS
BEGIN
....
-- private part for "ORACLE-WORLD" user only
IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'ORACLE-WORLD' THEN
...
END IF;
...
END;
You can, of course, monitor who deleted data from a table by using a trigger and SYS_CONTEXT for ip_address, host_name and so on.
See the list of the most commonly used sys_context parameters. For the full list, see the Oracle Doc pages pls 🙂
- ACTION
- BG_JOB_ID
- CLIENT_INFO
- CURRENT_SCHEMA
- CURRENT_SQL
- CURRENT_USER
- CURRENT_USERID
- DB_DOMAIN
- DB_NAME
- FG_JOB_ID
- HOST
- IP_ADDRESS
- LANGUAGE
- MODULE
- NLS_CALENDAR
- NLS_DATE_FORMAT
- OS_USER
- PROXY_USER
- SERVER_HOST
- SERVICE_NAME
- SESSION_USER
- SID
- TERMINAL