VPD Policy Tester

My VPD policies were causing troubles when I was running the Forms: ORA-28113 Policy Predicate Has Error . I could not easily figure out which Policy it was, as my data manipulation caused several triggers to fire that in turn performed queries and DML. So I wrote a simple procedure that can help me test all policies and find which ones are invalid.

It’s simple and really useful. Ideally it would disable all policies except the one being tested to be sure that any error is caused by that one. Feel free to add that functionality, I am under some time pressure right now.

create or replace
procedure policy_tester
is
  l_count number;
  procedure pl(p_text in varchar2)
  is
  begin
    dbms_output.put_line(p_text);
  end pl;
begin
  for ply in ( select object_name, object_owner
               ,      policy_name , package
               from   all_policies
             ) loop
    begin
      execute immediate 'select count(*) from '||ply.object_owner||'.'||ply.object_name
      into l_count;
      pl('Policy '||ply.policy_name||' on '||ply.object_owner||'.'||ply.object_name||' seems ok. ('||l_count||' records found)');
    exception
      when others
      then
        pl('Error ('||sqlerrm||') in policy '||ply.policy_name||' on '||ply.object_owner||'.'||ply.object_name);
    end;
  end loop;
end policy_tester;
/

Example output:

Policy PKS_CET_DML_ABS_AUT on AGS.AGS_CATALOGUS_ELEMENTEN seems ok. (0 records
found)
Policy PKS_V2400CPT_FUN_AUT on AGS.AGS_V_2400F_CPT seems ok. (0 records found)
Policy PKS_V2400CGP_FUN_AUT on AGS.AGS_V_2400F_CGP seems ok. (10 records found)
Policy PKS_V3100_FUN_AUT on AGS.AGS_V_3100F_PLN seems ok. (1 records found)
Policy PKS_V3201_FUN_AUT on AGS.AGS_V_3200F_PLN seems ok. (1 records found)
Error (ORA-28113: policy predicate has error) in policy PKS_V3301_FUN_AUT on
AGS.AGS_V_3300F_PLN
Policy PKS_V3401_FUN_AUT on AGS.AGS_V_3400F_PSR seems ok. (0 records found)
Policy PKS_V4100_FUN_AUT on AGS.AGS_V_4100F_RMG seems ok. (0 records found)
Policy PKS_V4200_FUN_AUT on AGS.AGS_V_4200F_RMG seems ok. (1 records found)
Policy PKS_V4300_FUN_AUT on AGS.AGS_V_4300F_RMG seems ok. (0 records found)
Policy PKS_V4400_FUN_AUT on AGS.AGS_V_4400F_PSR seems ok. (0 records found)
Policy PKS_V5101_FUN_AUT on AGS.AGS_V_5101F_PSR seems ok. (1 records found)
Policy PKS_V5102_FUN_AUT on AGS.AGS_V_5102F_MTG seems ok. (0 records found)
Policy PKS_V5201_FUN_AUT on AGS.AGS_V_5201F_MTG seems ok. (0 records found)
Error (ORA-28113: policy predicate has error) in policy PKS_V5302_FUN_AUT on
AGS.AGS_V_5300F_MTG
Policy PKS_V5301_FUN_AUT on AGS.AGS_V_5300F_PSR seems ok. (0 records found)
...