DBMS_SESSION Package
1. Purpose
This document describes the application-context subset of the Oracle-compatible
DBMS_SESSION package provided by IvorySQL’s built-in ivorysql_ora extension.
The package stores session-scoped (namespace, attribute, value) entries that can be
read back with SYS_CONTEXT and used to drive row-level security policies.
The package is installed automatically when an IvorySQL cluster is initialized in
Oracle compatibility mode; no additional CREATE EXTENSION step is required.
2. Functional Description
-
Context values are kept in a per-backend hash table. They are visible only to the session that created them and disappear when the session ends.
-
Namespace and attribute names are case-insensitive. They are folded to upper case when stored, so a value set with
SET_CONTEXT('app', 'usr', …)can be read withSYS_CONTEXT('APP', 'USR'). -
A namespace and an attribute may each be up to 255 bytes long; a value may be up to 4000 bytes. Longer namespace/attribute names and values are rejected with an error.
-
Setting an attribute that already exists overwrites its previous value. Setting a
NULLvalue stores the attribute with aNULLvalue; it does not remove the attribute. -
Reading an unknown namespace or attribute returns
NULL. -
SYS_CONTEXTreads the session context store for every namespace other than the built-inUSERENVandSYS_SESSION_ROLESnamespaces. -
DISCARD ALLandDISCARD PACKAGESclear all session context values, so a pooled connection does not leak one client’s context to the next client.
3. Supported Interfaces
| Interface | Description |
|---|---|
|
Stores or overwrites one attribute value in the current session. |
|
Removes one attribute. When |
|
Removes every attribute of the namespace. |
|
Resets all PL/iSQL package state in the session so that package initialization runs again on the next access. It does not clear application-context values. |
|
Set-returning function that lists all context entries of the current session. It
returns one row per entry with the columns |
4. Syntax
DBMS_SESSION.SET_CONTEXT(namespace VARCHAR2, attribute VARCHAR2, value VARCHAR2);
DBMS_SESSION.CLEAR_CONTEXT(namespace VARCHAR2, attribute VARCHAR2 DEFAULT NULL);
DBMS_SESSION.CLEAR_ALL_CONTEXT(namespace VARCHAR2);
DBMS_SESSION.RESET_PACKAGE;
SELECT * FROM sys.dbms_session_list_context();
| Parameter | Description |
|---|---|
|
Context namespace. Maximum 255 bytes; must not be |
|
Attribute name inside the namespace. Maximum 255 bytes. For |
|
Attribute value. Maximum 4000 bytes. A |
5. Examples
5.1. Store and read a value
CALL dbms_session.set_context('app_ctx', 'user_id', '42');
CALL dbms_session.set_context('app_ctx', 'role', 'manager');
SELECT sys_context('app_ctx', 'user_id') AS user_id,
sys_context('app_ctx', 'role') AS role;
Result:
user_id | role
---------+---------
42 | manager
(1 row)
Setting the same attribute again overwrites the previous value:
CALL dbms_session.set_context('app_ctx', 'user_id', '99');
SELECT sys_context('app_ctx', 'user_id') AS user_id;
user_id
---------
99
(1 row)
5.2. Case-insensitive names
CALL dbms_session.set_context('app_ctx', 'mixedAttr', 'mval');
SELECT sys_context('APP_CTX', 'MIXEDATTR') AS upper_read,
sys_context('app_ctx', 'mixedattr') AS lower_read,
sys_context('App_Ctx', 'MixedAttr') AS mixed_read;
All three columns return mval. An overwrite or a clear operation that uses a
different case reaches the same entry.
5.3. Unknown names and NULL values
-- Unknown attribute or namespace: NULL
SELECT sys_context('app_ctx', 'missing') IS NULL AS missing_is_null;
SELECT sys_context('no_such_ns', 'x') IS NULL AS ns_is_null;
-- An explicit NULL value is stored and reads back as NULL
CALL dbms_session.set_context('app_ctx', 'nullable', NULL);
SELECT sys_context('app_ctx', 'nullable') IS NULL AS value_is_null;
5.4. Clear attributes and namespaces
-- Clear one attribute
CALL dbms_session.clear_context('app_ctx', 'role');
SELECT sys_context('app_ctx', 'role') IS NULL AS role_cleared;
-- Clear the whole namespace by omitting the attribute
CALL dbms_session.clear_context('app_ctx');
SELECT sys_context('app_ctx', 'user_id') IS NULL AS app_ctx_cleared;
-- Equivalent form: CLEAR_ALL_CONTEXT
CALL dbms_session.set_context('other_ns', 'k1', 'v1');
CALL dbms_session.clear_all_context('other_ns');
SELECT sys_context('other_ns', 'k1') IS NULL AS other_ns_cleared;
5.5. List all context entries
CALL dbms_session.set_context('app_ctx', 'user_id', '99');
CALL dbms_session.set_context('app_ctx', 'role', 'manager');
CALL dbms_session.set_context('other_ns', 'k1', 'v1');
SELECT * FROM sys.dbms_session_list_context()
ORDER BY namespace, attribute;
namespace | attribute | value
-----------+-----------+---------
APP_CTX | ROLE | manager
APP_CTX | USER_ID | 99
OTHER_NS | K1 | v1
(3 rows)
Note that the stored names are upper-cased, and that the output order of the function
itself is unspecified; add ORDER BY when a deterministic order is required.
5.6. Drive a row-level security policy
Application context is commonly used to pass the current application user to a row-level security policy. Queries must run as a role that does not own the table, because table owners and superusers bypass row-level security.
CREATE TABLE docs (id int, owner text, body text);
INSERT INTO docs VALUES (1, 'alice', 'a-doc'), (2, 'bob', 'b-doc');
ALTER TABLE docs ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_owner ON docs USING (owner = sys_context('app', 'usr'));
CREATE ROLE dbms_sess_rls NOSUPERUSER;
GRANT SELECT ON docs TO dbms_sess_rls;
CALL dbms_session.set_context('app', 'usr', 'alice');
SET ROLE dbms_sess_rls;
SELECT * FROM docs ORDER BY id; -- only alice's row
RESET ROLE;
CALL dbms_session.set_context('app', 'usr', 'bob');
SET ROLE dbms_sess_rls;
SELECT * FROM docs ORDER BY id; -- only bob's row
RESET ROLE;
-- After the context is cleared the predicate is NULL and no row is visible
CALL dbms_session.clear_context('app');
SET ROLE dbms_sess_rls;
SELECT * FROM docs ORDER BY id; -- 0 rows
RESET ROLE;
DROP TABLE docs;
DROP ROLE dbms_sess_rls;
5.7. Reset PL/iSQL package state
RESET_PACKAGE resets package state, but leaves application-context values intact:
CREATE OR REPLACE PACKAGE rp_pkg IS
g_n NUMBER := 0;
FUNCTION get_n RETURN NUMBER;
FUNCTION set_n(n NUMBER) RETURN NUMBER;
END rp_pkg;
/
CREATE OR REPLACE PACKAGE BODY rp_pkg IS
FUNCTION get_n RETURN NUMBER AS
BEGIN
RETURN g_n;
END;
FUNCTION set_n(n NUMBER) RETURN NUMBER AS
BEGIN
g_n := n;
RETURN n;
END;
END rp_pkg;
/
SELECT rp_pkg.set_n(42);
SELECT rp_pkg.get_n; -- 42
CALL dbms_session.reset_package();
SELECT rp_pkg.get_n; -- 0: package initialization ran again
CALL dbms_session.set_context('rp_ns', 'key', 'preserved');
CALL dbms_session.reset_package();
SELECT sys_context('rp_ns', 'key') AS ctx_after_reset; -- preserved
DROP PACKAGE rp_pkg;
6. Behavioral Differences from Oracle
| Scenario | Oracle DBMS_SESSION | IvorySQL DBMS_SESSION |
|---|---|---|
Name length |
Namespace and attribute are limited to 128 bytes |
Up to 255 bytes |
Value length |
Up to 4000 bytes |
Up to 4000 bytes |
Case handling |
Names are case-insensitive |
Names are case-insensitive and are stored in upper case |
|
Package procedure with an |
Not exposed as a package procedure. Use the set-returning function
|
|
Not applicable |
Clears all session context values |
|
Resets package state |
Resets PL/iSQL package state; application-context values are preserved |
7. Notes and Limitations
-
The context store is session-local. Values set in one session are never visible in another session, even when both sessions use the same namespace and attribute names.
-
SET_CONTEXTrejects aNULLnamespace or attribute, and rejects names of 256 bytes or more and values longer than 4000 bytes. -
Because
SYS_CONTEXTis evaluated when a query runs, changing a context value takes effect for the next query in the same session. -
For connection pools, run
DISCARD ALL(orDISCARD PACKAGES) before handing a connection to another client.