Oracle PL/SQL Tutorial/System Packages/dbms session

DBMS_SESSION.IS_ROLE_ENABLED

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>

dbms_session.set_context

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>

DBMS_SESSION.SET_SQL_TRACE

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>

Use dbms_session.AppCtxTabTyp and dbms_session.list_context to display context

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>