The Oracle (tm) Users' Co-Operative FAQ

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.

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