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
- most often USERENV
- to see them all -> SELECT namespace FROM dba_context;
- 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
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 🙂