oracle - Expected UDT got NUMBER in Virtual Private Database Policy -

oracle - Expected UDT got NUMBER in Virtual Private Database Policy -

i have next problem: want implement 'row level security policy' table in database , added function:

function app_user_is_master_owner( schema_in in varchar2, object_in in varchar ) homecoming varchar2 return_value varchar2(100); begin select 'owner_fk = ' || (select mo.owner_id master_owner mo upper(mo.owner_name) = sys_context('userenv', 'session_user')) || ' or owner_fk empty' return_value dual; homecoming return_value; end app_user_is_master_owner;

i called add_policy dbms_rls add together other policys

begin dbms_rls.add_policy( object_schema => 'my_schema', object_name => 'master_data', policy_name => 'app_user_is_mo_policy', function_schema => 'my_schema', policy_function => 'my_rls_policys.app_user_is_master_owner', statement_types => 'select, insert, update, delete' ); end;

when phone call function within sql plus without adding rls policy expected 'where' clause returned

sql> select my_rls_policys.app_user_is_master_owner('a','a') dual; my_rls_policys.app_user_is_master_owner('a','a') -------------------------------------------------------------------------------- owner_fk = 4000 or owner_fk empty

but if phone call vpd-policy there error message.

sql> select * master_data; select * master_data * error @ line 1: ora-00932: inconsistent datatypes: expected udt got number

i guess it's because result select , not varchar, adding to_char neither "select to_char(...) return_value" nor "return to_char(return_value)" seems solve problem.

other policys works fine.

thanks help.

matthias

try fetching owner variable , concatenate

function app_user_is_master_owner( schema_in in varchar2, object_in in varchar ) homecoming varchar2 predicate varchar2(100); owner_id master_owner.owner_id%type; begin select owner_id owner_id master_owner upper(owner_name) = sys_context('userenv', 'session_user'); predicate := '(owner_fk = ' || owner_id || ' or owner_fk null)'; homecoming predicate; end app_user_is_master_owner;

p.s not sure is empty means ... changed is null.

oracle plsql row-level-security