8.4.1 The SYS_CONTEXT Function

The header for the SYS_CONTEXT function is:

FUNCTION SYS_CONTEXT (
   namespace VARCHAR2, 
   attribute VARCHAR2) 
RETURN VARCHAR2;

It returns the value associated with attribute as defined in the specified context namespace.

In addition to your own application context information, you can retrieve information about your current connection by calling SYS_CONTEXT as follows:

SYS_CONTEXT ('USERENV', attribute)

where attribute can be any of the values listed in Table 8.3.

Use the following script to examine each of these values:

/* Filename on companion disk: showucntxt.sql */
DECLARE
   PROCEDURE showenv (str IN VARCHAR2) IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (
         str || '=' || SYS_CONTEXT ('USERENV', str));
   END;
BEGIN
   showenv ('NLS_CURRENCY');
   showenv ('NLS_CALENDAR');
   showenv ('NLS_DATE_FORMAT');
   showenv ('NLS_DATE_LANGUAGE');
   showenv ('NLS_SORT');
   showenv ('SESSION_USER');
   showenv ('CURRENT_USER');
   showenv ('CURRENT_SCHEMA');
   showenv ('CURRENT_SCHEMAID');
   showenv ('SESSION_USERID');
   showenv ('CURRENT_USERID');
   showenv ('IP_ADDRESS');
END;
/

8.4.2 LIST_CONTEXT: Obtaining the List of Defined Context Attributes

The DBMS_SESSION built-in package provides a procedure that retrieves the list of defined attributes and values for all contexts in your session. Here is the header of that procedure:

PROCEDURE DBMS_SESSION.LIST_CONTEXT (
   list OUT DBMS_SESSION.AppCtxTabTyp, 
   lsize OUT number);

where lsize is the number of elements in list, and list is an index-by table of records. Each record has this format:

TYPE DBMS_SESSSION.AppCtxRecTyp IS RECORD ( 
   namespace VARCHAR2(30), 
   attribute VARCHAR2(30), 
   value VARCHAR2(4000));

where namespace and attribute have the meanings described for SYS_CONTEXT.

Here is a program that utilizes this procedure to retrieve and display all defined context attributes:

/* Filename on companion disk: showcntxt.sp */
CREATE OR REPLACE PROCEDURE show_context_info 
IS
   context_info DBMS_SESSION.AppCtxTabTyp;
   info_count PLS_INTEGER;
   indx PLS_INTEGER;
BEGIN
   DBMS_SESSION.LIST_CONTEXT (
      context_info,
      info_count);
   indx := context_info.FIRST;   
   LOOP
      EXIT WHEN indx IS NULL;
      DBMS_OUTPUT.PUT_LINE (
         context_info(indx).namespace || '.' ||
         context_info(indx).attribute || ' = ' ||
         context_info(indx).value);
      indx := context_info.NEXT (indx);
   END LOOP;   
END;
/

Here is a script and output that demonstrates the use of this procedure (building upon contexts and packages defined by first running the earth.pkg and prison.pkg scripts):

/* Filename on companion disk: showcntxt.tst */
BEGIN 
   /* Set context information.*/
   earth_pkg.set_contexts; 
   prison_pkg.set_contexts; 
   show_context_info;
END;
/

INCARCERATION_FACTORS.CLASS = poor
POLLUTION_INDICATORS.SMOG = dense
INCARCERATION_FACTORS.EDUCATION = minimal
POLLUTION_INDICATORS.ACIDRAIN = corrosive

8.4.3 Context Data Dictionary Views

Oracle provides the data dictionary views listed in Table 8.4, which you can query to obtain information about policies defined in or accessible to your schema.

The columns for the *_POLICIES views are described in Table 8.5. These values are set through calls to the DBMS_RLS programs ADD_POLICY and ENABLE_POLICY, described in Chapter 7.

The columns for the *_CONTEXT views are described in Table 8.6.

You can, of course, write queries and stored programs to access this information. Here is a procedure that you can use to drop one or all of your policies:

/* Filename on companion disk: droppol.sp */
CREATE OR REPLACE PROCEDURE drop_policies (
   objname IN VARCHAR2,
   polname IN VARCHAR2 := '%',
   objschema IN VARCHAR2 := NULL)
   AUTHID CURRENT_USER
IS
BEGIN
   FOR rec IN (
      SELECT object_owner, 
             object_name, 
             policy_name
        FROM ALL_POLICIES
       WHERE object_owner LIKE NVL (objschema, USER)
         AND object_name LIKE objname
         AND policy_name LIKE polname)
   LOOP
      DBMS_RLS.DROP_POLICY (
         rec.object_owner, rec.object_name, rec.policy_name);
   END LOOP;
END;
/

Notice that I use AUTHID CURRENT_USER to make sure that the procedure will only drop policies for the tables and views for which the CURRENT_USER has the right access privileges, regardless of who owns the procedure itself. The WHERE clause will further limit the policies to those created for the objects owned by the CURRENT_USER.

You can also use the DDL statement DROP CONTEXT to drop a context or policy directly within a SQL execution environment (or via dynamic SQL).