Oracle PL/SQL Tutorial/System Packages/dbms session

DBMS_SESSION.IS_ROLE_ENABLED

   <source lang="sql">

SQL> SQL> BEGIN

 2     IF DBMS_SESSION.IS_ROLE_ENABLED("ADMINSTRATOR") THEN
 3        DBMS_OUTPUT.PUT_LINE("Current Role Administrator");
 4     ELSIF DBMS_SESSION.IS_ROLE_ENABLED("MANAGER") THEN
 5        DBMS_OUTPUT.PUT_LINE("Current Role Manager");
 6     ELSE
 7        DBMS_OUTPUT.PUT_LINE("Current Role Operator");
 8        
 9     END IF;
10  END;
11  /

Current Role Operator PL/SQL procedure successfully completed. SQL> SQL></source>

dbms_session.set_context

   <source lang="sql">

SQL> SQL> create or replace procedure set_dept_ctx(p_attr in varchar2,p_val in varchar2) is

 2  begin
 3      dbms_session.set_context("DEPT_CTX", p_attr, p_val);
 4  end;
 5  /

Procedure created. SQL> SQL></source>

DBMS_SESSION.SET_SQL_TRACE

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE core_process IS

 2  BEGIN
 3     IF USER = "PLSQL_USER" THEN
 4        DBMS_SESSION.SET_SQL_TRACE(TRUE);
 5        DBMS_OUTPUT.PUT_LINE("Tracing is turned on...");
 6     END IF;
 7     DBMS_SESSION.SET_SQL_TRACE(FALSE);
 8     DBMS_OUTPUT.PUT_LINE("Tracing is turned off.");
 9  END;
10  /

Procedure created. SQL> SQL></source>

Use dbms_session.AppCtxTabTyp and dbms_session.list_context to display context

   <source lang="sql">

SQL> SQL> declare

 2      l_AppCtx     dbms_session.AppCtxTabTyp;
 3      l_size        number;
 4  begin
 5      dbms_session.list_context( l_AppCtx, l_size );
 6      for i in 1 .. l_size loop
 7          dbms_output.put( l_AppCtx(i).namespace || "." );
 8          dbms_output.put( l_AppCtx(i).attribute || " = " );
 9          dbms_output.put_line( l_AppCtx(i).value );
10      end loop;
11  end;
12  /

DR$APPCTX.IDXID = REGISTRY$CTX.NAMESPACE = SERVER PL/SQL procedure successfully completed. SQL> SQL> SQL></source>