VPD policy to form 2 where clauses

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Re: VPD policy to form 2 where clauses

Postby me_lucky » Thu Sep 19, 2013 11:59 am

d_predicate :=
'(
CONT_NU=sys_context(''CONT_CONTEXT'',''CONT_NU'')
OR
CHAS_NU=sys_context(''CHAS_CONTEXT'',''CHAS_NU'')
)';
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Thu Sep 19, 2013 12:53 pm

Hi.

That certainly looks like what you asked for.

I would suggest you do the following in an SQL*plus session:

1) Set the context variables to the correct values.
2) Run the select statement manually, with the predicate added.

If you get the correct result, then the predicate is not the problem. Chances are, the context settings are not what you think they should be.

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
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD policy to form 2 where clauses

Postby me_lucky » Fri Sep 20, 2013 12:02 pm

Hello Tim,

still the same behavior.
I tested as below:

SQL> exec pkg_vpd_context.P_SET_CONTNU('31232');

PL/SQL procedure successfully completed

SQL> exec pkg_vpd_context.P_SET_CHASNU('31232');;

PL/SQL procedure successfully completed

SQL>
SQL> SELECT COUNT(*)
2 FROM CONT_CHAS_TAB a
3 where CONT_NU =
4 sys_context('CONT_CONTEXT','CONT_NU')
5 OR CHAS_NU =
6 sys_context('CHAS_CONTEXT', 'CHAS_NU')
7 /
76 rows selected

Where am suppose to get 3442 rows.
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Fri Sep 20, 2013 1:43 pm

Hi.

So VPD is working fine as it is producing the same result as your query.

What does this give you?

Code: Select all
SELECT COUNT(*)
FROM CONT_CHAS_TAB a
where CONT_NU = '31232' OR CHAS_NU = '31232';


Note. I assume you've disabled your VPD policies before doing this test, or it will still be applying the predicate on top. :)

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
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: VPD policy to form 2 where clauses

Postby me_lucky » Fri Sep 20, 2013 2:34 pm

SELECT COUNT(*)
FROM CONT_CHAS_TAB a
where CONT_NU = '31232' OR CHAS_NU = '31232';
give... 3442 rows.

anyhow i'l once again verify properly and let you know.

Thanks a ton Tim.
:-)
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: VPD policy to form 2 where clauses

Postby Tim... » Fri Sep 20, 2013 3:32 pm

Hi.

So it looks to me like the context values are not getting set correctly, which is why when you do the SYS_CONTEXT calls you are not getting the rowcount you expect. Once the context values are set correctly, I would expect this to work... :)

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
Tim...
Site Admin
 
Posts: 17955
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Previous

Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest