Table of Contents
This chapter briefly introduces the DBMS_SESSION package, and describes how to use the procedures and functions of the package.
This section describes the procedures and functions provided by the DBMS_SESSION package, in alphabetical order.
Clears all attribute values of a context. This procedure can only be called inside the package specified through the CREATE CONTEXT DDL.
Details about the CLEAR_ALL_CONTEXT procedure are as follows:
Prototype
DBMS_SESSION.CLEAR_ALL_CONTEXT
(
namespace IN VARCHAR2
)
Parameter
| Parameter | Description |
|---|---|
| namespace | Namespace of the context whose attribute values are to be cleared. |
Clears attribute value(s) of a context. This procedure can only be called inside the package specified through the CREATE CONTEXT DDL.
Details about the CLEAR_CONTEXT procedure are as follows:
Prototype
DBMS_SESSION.CLEAR_CONTEXT
(
namespace IN VARCHAR2,
client_id IN VARCHAR2 DEFAULT NULL,
attribute IN VARCHAR2 DEFAULT NULL
)
Parameter
| Parameter | Description |
|---|---|
| namespace | Namespace of the context whose attribute values are to be cleared. |
| client_id | Valid only when the context is created with the 'ACCESSED GLOBALLY' clause. If set to NULL, the specified attribute value(s) are cleared when the SET_CONTEXT procedure is called. |
| attribute | Name of the attribute whose value is to be cleared. If not set, all attribute values of the context connected to the client_id are cleared. |
Example
create or replace package body sec_pkg is
procedure clear (name varchar2) as
begin
dbms_session.clear_context (name);
end;
end;
/
Sets the identifier of the current session to NULL.
Details about the CLEAR_IDENTIFIER procedure are as follows:
Prototype
DBMS_SESSION.CLEAR_IDENTIFIER
Example
BEGIN DBMS_SESSION.CLEAR_IDENTIFIER; END; / SELECT CLIENT_IDENTIFIER FROM V$SESSION;
Returns a list of contexts that can be accessed by the current session.
Details about the LIST_CONTEXT function are as follows:
Prototype
TYPE AppCtxRecTyp IS RECORD (namespace VARCHAR2(30),
attribute VARCHAR2(30),
value VARCHAR2(256));
TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp INDEX BY BINARY_INTEGER;
DBMS_SESSION.LIST_CONTEXT(list OUT AppCtxTabTyp,
size OUT NUMBER);
Parameter
| Parameter | Description |
|---|---|
| list | INDEX BY table where context details are to be saved. |
| size | Number of entries to be returned to the buffer. |
Sets an attribute value of a context. The username and client_id parameters can only be used only for globally accessed contexts.
The client_id parameter is case-sensitive and its value must be identical to when the SET_IDENTIFIER procedure was called. Different attribute values can be set for different client_id values.
If the username parameter is specified for a globally accessed context namespace, only the specified database user can access the attribute. If not specified, all database users can access the attribute.
This procedure can be called inside the package specified through CREATE CONTEXT DDL. The attribute values are maintained only while the session is maintained. If this procedure is called when the attribute value has already been set, it overwrites the existing value.
Details about the SET_CONTEXT procedure are as follows:
Prototype
DBMS_SESSION.SET_CONTEXT
(
namespace IN VARCHAR2,
attribute IN VARCHAR2,
value IN VARCHAR2,
username IN VARCHAR2 DEFAULT NULL,
client_id IN VARCHAR2 DEFAULT NULL
)
Parameter
| Parameter | Description |
|---|---|
| namespace | Namespace of the context whose attribute value is to be set. |
| attribute | Context attribute name. |
| value | Value of the attribute to set. |
| username | Name of the database user who can access the attribute. It can only be specified for a globally accessed context namespace, and can only be accessed by the specified database user. If not specified, all database users can access the attribute. (Default value: NULL) |
| client_id | Client identifier of the user who can view the session context value. It is case-sensitive and its value must be identical to when the SET_IDENTIFIER procedure was called. It can only be specified for a globally accessed context namespace. (Default value: NULL) |
Example
create or replace package body sec_pkg is
procedure set_attr (name varchar2, attr varchar2, val varchar2) as
begin
dbms_session.set_context (name, attr, val);
end;
end;
/
Sets the CLIENT_IDENTIFIER value of a session. The set value can be queried from the CLIENT_IDENTIFIER column in V$SESSION, or using the SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER') built-in function.
Details about the SET_IDENTIFIER procedure are as follows:
Prototype
DBMS_SESSION.SET_IDENTIFIER
(
client_id IN VARCHAR2
)
Parameter
| Parameter | Description |
|---|---|
| client_id | Client identifier. A value longer than 64 bytes is ignored. |
DECLARE client_id VARCHAR2(64); BEGIN client_id := 'MY_CLIENT_ID'; DBMS_SESSION.SET_IDENTIFIER(client_id); END; / SELECT CLIENT_IDENTIFIER FROM V$SESSION;
Returns a unique session identifier of 64 bytes in size.
Details about the UNIQUE_SESSION_ID function are as follows:
Prototype
DBMS_SESSION.UNIQUE_SESSION_ID RETURN VARCHAR2
Example
DECLARE u_sess_id VARCHAR2(64); BEGIN u_sess_id := DBMS_SESSION.UNIQUE_SESSION_ID; DBMS_OUTPUT.PUT_LINE(u_sess_id); END; /
Closes a DBLINK.
Details are as follows:
Prototype
DBMS_SESSION.CLOSE_DATABASE_LINK
(
dblink IN VARCHAR2
)
Example
DECLARE
BEGIN
DBMS_SESSION.CLOSE_DATABASE_LINK('link1');
END;
/
Checks whether the role is enabled for the session that called this function.
Details are as follows:
Prototype
DBMS_SESSION.IS_ROLE_ENABLED
(
rolename IN VARCHAR2
)
RETURN BOOLEAN
Example
DECLARE
result BOOLEAN;
BEGIN
result := DBMS_SESSION.IS_ROLE_ENABLED('CONNECT');
END;
/
Modifies the role enabled for the session that called this procedure. This is the same as executing a DDL that begins with 'SET ROLE' and the input is the string that comes after the 'SET ROLE' statement.
Details are as follows:
Prototype
DBMS_SESSION.SET_ROLE
(
rolecmd IN VARCHAR2
)
Example
DECLARE
BEGIN
DBMS_SESSION.SET_ROLE('ALL');
DBMS_SESSION.SET_ROLE('ALL EXCEPT CONNECT');
END;
/