VPD and hard parsing

  • From: "Schauss, R. Peter (IT Solutions)" <peter.schauss@xxxxxxx>
  • To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 1 Jul 2009 15:52:40 -0500

A while back I implemented a VPD scheme to limit user access for
reporting purposes.  The way that I coded the functions which generated
the predicates was such that, for the application they returned NULL,
i.e. no restriction, and for specified reporting users, they generated
restrictive code to be appended to the where clause.  For example:

  function probsummarym1_sec(D1 varchar2, d2 varchar2)
    return varchar2 is
    uname varchar2(30);
    d_predicate varchar2(2000);
        d_predicate := null;
        select sys_context('ADHOC_ACCESS','USERNAME') into uname from
        if (uname is not null ) then
            d_predicate := 'company in (select company from
custom.t_user_company_access where
        end if;
        return d_predicate;
    end probsummarym1_sec;

Will this force a hard parse if it returns NULL?

This was on an Oracle

FWIW,  I turned this project over to another DBA about a two weeks after
they went production, so I have no direct indication as to whether my
VPD implementation was causing performance problems.

Peter Schauss

Other related posts:

  • » VPD and hard parsing - Schauss, R. Peter (IT Solutions)