|Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
|Date written: 26th July 2001
Oracle version(s): 7.3 - 9.0.1
|There are times when you need to use, and re-use, a value such as a department id, or country code throughout a session, but the value to be used is dependent on (for example) the user id. You set up a reference table to hold such values - but if you have to keep querying the table, the overhead could be significant. Is there a better option.|
Back to index of questions
There are at least two strategies to follow. One can be used in any version of Oracle that runs PL/SQL, but has some limitations, the other is restricted to 8.1 and higher and is more robust and secure.
The older method requires you to create a package containing a list of declared variables and an initialization routine. The first time a user references anything in this package, the initialization code is run, and the values are set for the session. For example.
create or replace package pkg_globals is visible_number number(32); function hidden_number return number; end; / create or replace package body pkg_globals as invisible_number number; function hidden_number return number is begin if invisible_number is null then -- do something complicated here invisible_number := 99; end if; return invisible_number; end; begin -- do something complicated here visible_number := 99; end; /
One drawback to the simplest approach is that the user can see the global variables, and therefore could change them at will. To eliminate this threat, you need to use the slighlty more complex code, which makes the variables private, but declares functions to return their values. A more significant drawback to the approach is the overhead - all that PL/SQL calling costs in CPU and latches - and there are performances traps if you start using the packaged functions to drive SQL queries.
The modern method is to take advantage of logon triggers contexts, and the sys_context() feature.
A context is a named list of 'environment variables'. When you create the name for a context, you include the name of a procedure that is allowed to modify the variables that are created in an instantion of that context - so if you name a procedure that the user is not allowed to execute, the user cannot change their copy of the context.
If you then set up a logon trigger which calls the procedure to set the context, you can guarantee that the user's context is set, irrespective of the tool they use to connect to the database.
Finally, any calls to the sys_context() are treated by the optimizer as a form of 'pseudo-constant', so the overheads associated with calling a normal function from within SQL are eliminated.
create or replace context jpl_context using jpl.context_package; create or replace package context_package as procedure set_context; end; / create or replace package body context_package as procedure set_context is begin -- do something complicated here dbms_session.set_context( 'jpl_context', 'var_name', 'var_value' ); end; end; / create or replace trigger logon_context after logon on jpl.schema -- test it on a schema first -- before trying it 'on database' begin context_package.set_context; end; / connect jpl/jpl SQL> execute dbms_output.put_line(sys_context('jpl_context','var_name')); var_value PL/SQL procedure successfully completed.
Further reading: SQL Reference Manual - Create Context, Create Trigger.
Back to top
Back to index of questions