Greetings.
We are planning to add a new column in all tables of schema for a common where clause for all the queries running in our application. The column name will be the same in all the tables. When queried any table in our schema the newly added column should also be part of the where clause. i.e. If a existing query in our application is as following
- Code: Select all
--Existing Query
--==============
Select tC1,tC2,tC3,tC4
from Table_Name
t1,t2
where t1.tc1 = t2.tc3
--New Query As expected
--=====================
Select tC1,tC2,tC3,tC4
from Table_Name
t1,t2
where
t1.tc1 = t2.tc3
and t1.New_col = t2.New_Col --Newly added columns
After reading upon Application Contexts and sys_context, we could achieve this partially
but had to create a view for each table and query the view instead of the table.
This is what which was tried
- Code: Select all
/* Table Creation Script */
create table sys_ctx (c1 varchar2(240) , c2 varchar2(240);
/* Insert Statements for table sys_ctx */ ;
insert into sys_ctx (C1, C2)
values ('call1', 'x');
insert into sys_ctx (C1, C2)
values ('call2', 'x');
insert into sys_ctx (C1, C2)
values ('call3', 'y');
insert into sys_ctx (C1, C2)
values ('call4', 'y');
insert into sys_ctx (C1, C2)
values ('call6', 'z');
insert into sys_ctx (C1, C2)
values ('call5', 'z');
commit;
/* Context Creation */
create or replace context rnd using rnd_prc;
/* Procedure Creation for setting the context */
create or replace procedure rnd_prc(p_in in varchar2 default 'x')
is
begin
dbms_session.set_context('rnd','x',p_in);
end rnd_prc;
/
/* View Creation with sys_context */
create or replace view sys_ctx_view as
select * from
sys_ctx
where c2 = sys_context('rnd','x');
-- C2 is newly Added Column where Sys Context is enabled
/* changing sys_cotext value through dbms_session called through rnd_prc procedure */
exec rnd_prc('y');
/*
Now When queried from sys_ctx_view after setting the sys_context value to 'Y', the records getting queried will only be those for which Col C2 equals 'y'. But its not possible to create 1000 view on a schema for 1000 tables. How to overcome this?
Few questions
1) Is Appllication Context, Global application context part of standard edition?
2) If its part of only Enterprise edition, is there any additional licenses over Enterprise or how it is.
Thanks in advance