The
dbms_session package provides a
number of useful procedures and functions
related to managing and/or controlling sessions.
Begin by breaking down those offerings along
related lines of usefulness starting with the
general purpose ones for simply getting or
setting session level attributes or
characteristics. They are all fairly
straightforward and simple, so no examples are
given for them.
—- Returns a unique id for
a session
DBMS_SESSION.UNIQUE_SESSION_ID RETURN
VARCHAR2;
—- Returns whether current
session is still active
DBMS_SESSION.IS_SESSION_ALIVE (uniqueid
VARCHAR2) RETURN BOOLEAN;
—- Returns whether named
role is enabled for session
DBMS_SESSION.IS_ROLE_ENABLED (rolename
VARCHAR2) RETURN BOOLEAN;
—- Enables and disables
named role for session
—- Same as SQL command:
SET ROLE
DBMS_SESSION.SET_ROLE (role_cmd
VARCHAR2);
—- Permits setting
session’s various globalization (NLS)
settings
—- Same as SQL command:
ALTER SESSION SET nls_parameter = value
DBMS_SESSION.SET_NLS (param
VARCHAR2, value VARCHAR2);
—- Permits setting session
trace flag on or off
—- Same as SQL command:
ALTER SESSION SET SQL_TRACE = boolean
DBMS_SESSION.SET_SQL_TRACE
(sql_trace boolean);
—- Permits closing an open
database link
—- Same as SQL command:
ALTER SESSION CLOSE DATABSE LINK dblink_name
DBMS_SESSION.CLOSE_DATABASE_LINK (dblink
VARCHAR2);
—- Frees up unused memory
after large operations (> 100K)
DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
—- Permits session to
de-instantiate (i.e. unload from memory)
-- all packages and their
memory, cursors, global variables, etc
DBMS_SESSION.RESET_PACKAGE;
—- Permits session to
change the current resource consumer group
DBMS_SESSION.switch_current_consumer_group (
new_consumer_group
IN
VARCHAR2,
old_consumer_group
OUT VARCHAR2,
initial_group_on_error IN
BOOLEAN);
The remaining procedures and functions primarily
support row level security (RLS), also referred
to as virtual private databases (VPD) and fine
grained access control. Examples of using these
functions are shown in the section on row level
security, so identify their names, purpose,
parameters and defaults.
Set_identifier,
clear_set_identifier and
clear_identifier procedures permit
setting and clearing the client ID for the
session. The client ID of a session is used to
map it to some corresponding global application
context, which is necessary for RLS and/or VPD.
—- Permits setting the
session application-specific identtifier
DBMS_SESSION.SET_IDENTIFIER (client_id
VARCHAR2);
—- Permits clearing the
session application-specific identtifier
DBMS_SESSION.CLEAR_IDENTIFIER;
Now move onto procedures and functions
specifically for managing contexts for a
session. These are a little more complicated and
the following data type needs to be worked with
when listing a session’s active contexts:
TYPE AppCtxRecTyp IS
RECORD (
namespace VARCHAR2(30),
attribute VARCHAR2(30),
value
VARCHAR2(256));
TYPE AppCtxTabTyp IS TABLE
OF AppCtxRecTyp INDEX BY BINARY_INTEGER;
The
list_context procedure is also
rather simple; it returns an array of the
contexts using this data type and the count of
returned entries.
DBMS_SESSION.LIST_CONTEXT
(list OUT AppCtxTabTyp, size OUT NUMBER);
Here is an example of using this procedure:
.list_context_demo.sql script
SET SERVEROUTPUT ON
DECLARE
array_size INT;
array_recs DBMS_SESSION.AppCtxTabTyp;
BEGIN
array_size := 0;
DBMS_SESSION.LIST_CONTEXT (array_recs,
array_size);
for i in 1 .. array_size loop
DBMS_OUTPUT.PUT_LINE('Context Name =
' || array_recs(i).namespace);
DBMS_OUTPUT.PUT_LINE('...Atrribute =
' || array_recs(i).attribute);
DBMS_OUTPUT.PUT_LINE('...Value
= ' || array_recs(i).value);
end loop;
END;
/
The remaining three procedures are entirely for
managing the corresponding global application
context, which is necessary for RLS and/or VPD
and whose examples are in the section on role
level security.
Set_context
sets the specified context for a given
namespace, of which there are four types:
session local, globally initialized, externally
initialized, and globally accessed.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
NAMESPACE
|
VARCHAR2
|
IN
|
|
|
ATTRIBUTE
|
VARCHAR2
|
IN
|
|
|
VALUE
|
VARCHAR2
|
IN
|
|
|
USERNAME
|
VARCHAR2
|
IN
|
NULL
|
|
CLIENT_ID
|
VARCHAR2
|
IN
|
NULL
|
Table 6.133:
Set_context Parameters
Clear_context
clears
the named context for a given namespace.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
NAME_SPACE
|
VARCHAR2
|
IN
|
|
|
CLIENT_IDENTIFIER
|
VARCHAR2
|
IN
|
|
|
ATTRIBUTE
|
VARCHAR2
|
IN
|
|
Table 6.134:
Clear_context Parameters
Clear_all_context
clears all contexts for a given namespace.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
NAME_SPACE
|
VARCHAR2
|
IN
|
|
Table 6.135:
Clear_all_context Parameters
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
