This forum is currently locked. You can't register or post questions at this time. (read more)

Application context in PlSql

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Application context in PlSql

Postby Guru3i » Mon Jun 18, 2012 1:09 pm

Hi Tim,

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

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: Application context in PlSql

Postby Guru3i » Mon Jun 18, 2012 2:23 pm

Please use this for table creation and further script

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');

    /*



Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Application context in PlSql

Postby Tim... » Mon Jun 18, 2012 3:32 pm

Hi.

You can use the VPD functionality to add the predicate to the end of every query against every table.

http://www.oracle-base.com/articles/8i/ ... abases.php

It will automatically add the relevant predicates, so you don't have to mess about with views.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Re: Application context in PlSql

Postby Guru3i » Mon Jun 18, 2012 3:41 pm

Hi Tim,
Thanks. But Is this feature available in Standard Edition of Oracle 11g Release 2. If we are restricted to go with Standard, How else we can achieve our requirement? It will be a humongous effort to have this done programmaticly. Can you suggest any better approach/alternative in case we are to live with Standard.
Thanks again for your help

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Application context in PlSql

Postby Tim... » Mon Jun 18, 2012 8:47 pm

Hi.

Actually, it looks like VPD is an enterprise edition option, which makes me thing global application contexts may be also. It's worth confirming this with Oracle support as the free/paid-for options change with each release.

I don't know how else you can accomplish this in standard edition, other than doing it the hard way.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests