How can I set up a variable that is visible only to my
session and persists for the duration of the session .

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.

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.

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.