René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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 USERENVdeclare 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; / |