APEX

I'm trying to implement a VPD policy which should be really easy but I am pulling my hair out. What I want to do is partition by CHANNEL which will mean adding a channel_id to many tables once this works.

When I implement the following, even when my user has all channels assigned in staff_channel, I get a page can not be found message. It appears there is an error with the predicate but I have a test page where I am examining it and it looks fine. If I copy this and add it to the end of a select * from channel it works.

Any help greatly appreciated:

-- Drop the policy just in case it is defined already
begin
dbms_rls.drop_policy(object_name => 'channel',policy_name =>'vpd_channel');
exception when others then null;
end;
/
-- create a policy that creates a predicate based on channel
create or replace function f_staff_channels return varchar2 is
l_where varchar2(1000);
begin
-- When a connection is not made from ApEx, permit access on 1=1
if v('APP_STAFF_ID') is null then
l_where := '1=1';
else
-- create a default where clause including the non existent channel of -5
l_where := 'where channel_id in (-5,';
for x in (select channel_id
from staff_channel
where staff_id = v('APP_STAFF_ID')) loop
l_where := l_where || to_char(x.channel_id) || ',';
end loop;
l_where := substr(l_where, 1, length(l_where) - 1) || ')';
end if;
return l_where;
end;
/

-- Apply the policy
begin

dbms_rls.add_policy (
object_schema => 'Q_DEV',
object_name => 'CHANNEL',
policy_name => 'vpd_channel',
policy_function => 'f_staff_channels',
statement_types => 'SELECT, INSERT, UPDATE, DELETE'
);

end;
/