Setting a Context in PL/SQL

Creating the package to set the Context

Each Context is associated with a Package and a Key Value pair of that context can only be set from within this package. So, wee need to create a package which we'll call Context_package. Its only procedure is name_value which uses parameter n as a name (key) and v as the value.

create package context_package as
  procedure name_value(n varchar2, v varchar2);
end;

create package body context_package as
  procedure name_value(n varchar2, v varchar2) as
  begin
    -- can only be called within the package to which it belongs
    -- If you try to execute DBMS_SESSION.SET_CONTEXT you'll get an error, as shown here:
    -- ORA-01031: insufficient privileges
    dbms_session.set_context('TEST_CONTEXT',n,v);
  end;
end;

Now, we need to create a context, called test_context here:

create context test_context using context_package;

As noted before, each context is associated with a package. This package is indicated with the using clause of the create context command. Now, we can use context_package.name_value to set name value pairs into the context:

exec context_package.name_value('eins','one');
exec context_package.name_value('zwei','two');
exec context_package.name_value('drei','three');

SYS_CONTEXT to query values of keys

If we want to query the value of a key in the context, we have use sys_context:

exec dbms_output.put_line(sys_context('TEST_CONTEXT','zwei'));

This prints two

Predefined names of context USERENV

declare
  procedure userenv_key_to_value (k in varchar2) is
  begin
      dbms_output.put_line(rpad(k,20,' ') || '= ' || ##(sys_context,/ora/sql/sys_context.html)('userenv',k));
  end;
begin
  userenv_key_to_value('nls_currency;
  userenv_key_to_value('nls_calendar;
  userenv_key_to_value('nls_date_format;
  userenv_key_to_value('nls_date_language;
  userenv_key_to_value('nls_sort;
  userenv_key_to_value('session_user');
  userenv_key_to_value('current_user');
  userenv_key_to_value('current_schema');
  userenv_key_to_value('current_schemaid');
  userenv_key_to_value('session_userid');
  userenv_key_to_value('current_userid');
  userenv_key_to_value('ip_address');
end;
/


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback