|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
A
requirement that often comes up is to set-up a variable that is
visible for a session irrespective of the tools used to log-in to
oracle. With versions prior to 8i, package global variables were used
to achieve this functionality where a call to the package sets the
variable for the duration of the session. The downside to it is that
the variable can be changed from within the session unless defined as
a CONSTANT which forces the developer to use hard-coded values.
However, with oracle 8i, there is an additional functionality with the
availability of the SYS_CONTEXT() feature. Here's how it works:
In
this example the feature's being used to return the name of the
database:
Step 1 :
create the context (User needs
<create any context> privilege):
-- Defines a package
that is used in the next step to initialize environment variables
create or replace
context test_context using test_user.context_test_package;
Step 2 :
create the package or procedure
used in the context before :
create or replace package context_test_package as
procedure set_test_context;
end;
/
create or replace package body context_test_package as
procedure set_test_context is
var_database_name varchar2(100);
begin
---- Defines the name of the database
select decode(instr(global_name,'.'),0,global_name,substr(global_name,1,instr(global_name,'.')-1))
into var_database_name from global_name;
dbms_session.set_context('test_context','database_name',var_database_name);
end;
end context_test_package;
/
Step 3 : Define a
on-logon trigger at the schema level which sets the context for the
session :
create or replace trigger logon_test_context
after logon on test_user.schema
begin
context_test_package.set_test_context;
end;
/
After this, any time you
log as TEST_USER , the context is automatically set for the session.
You can get the values
as follows :
select
sys_context('test_context','database_name') from dual;
(or)
exec
dbms_output.put_line(sys_context('test_context','database_name'));
Using
this method makes the code more scalable since it can be used in any
database and the same functionality is achieved without making any
code changes. |
