Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL - VPD problem

48202Aug 30 2005 — edited Aug 31 2005
Greetings,
I am trying to configure a VPD for my database. I keep receiving the following error from the code as displayed below.

ORA-28113: policy predicate has error.

As far as I can tell the problem lies with the SQL statement from the trigger but all attempts to run this code alone give me various errors. (missing keyword in line 2 with the version displayed). I have tried many versions on this statement without success.

Any assistance would be appreciated

select company_no
into v_company_no
from companydb
where upper(companydb.dbuser) = user;

trigger:
---------------------
declare
v_company_no company.company_no%type;
begin

select company_no
into v_company_no
from companydb
where upper(companydb.dbuser) = user;

pck_vpd.set_company_no(v_company_no);

end;
-----------------------
package pck_vpd

as p_company_no company.company_no%type;

procedure set_company_no(v_company_no company.company_no%type);

function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;

end pck_vpd;
----------------------------------------------
package body pck_vpd

as
procedure set_company_no(v_company_no company.company_no%type) is
begin
p_company_no := v_company_no;
end set_company_no;

function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return ' company_no = ' || p_company_no;
end predicate;
end pck_vpd;

mjk
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 28 2005
Added on Aug 30 2005
1 comment
257 views