oracle ORA-00904: "E3": invalid identifier in function for vpd

Question

I am learning oracle vpd so I created the following tables:

create table employees ( empid varchar(30) primary key,empname varchar(30), dept varchar(30));
insert into employees values ('E1','mark','ACCOUNTING');
insert into employees values ('E2','john','SALES');
insert into employees values ('E3','vpdadmin', 'RESEARCH');


create table payroll (empid varchar(30), dept varchar(30), total int, taxes int, foreign key(empid) references employees(empid));
insert into payroll values ('E1','ACCOUNTING',2400,100);
insert into payroll values ('E2','SALES',2500,75);
insert into payroll values ('E3','RESEARCH',3000,110);

and then created policy function as follow:

create function policy_function (obj_schema varchar2, obj_name varchar2) 
return varchar2 is
v_dept employees.dept% TYPE;
v_id employees.empid% TYPE;
begin 
select dept into v_dept from employees where upper(EMPNAME)=SYS_CONTEXT('userenv','SESSION_USER');
select empid into v_id from employees where upper(EMPNAME)=SYS_CONTEXT('userenv','SESSION_USER');
if (v_dept != 'ACCOUNTING') then
return 'upper(EMPID)='|| v_id;
else
return '';
end IF ;
end policy_function;

and then:

begin dbms_rls.add_policy  (
user,
'payroll',
'policy_on',
user,
'policy_function',
'select'); 
end; 

but when I do select * from payroll from vpdadmin account I get error:

Error information for ORA-28113:
Logon user     : VPDADMIN
Table/View     : VPDADMIN.PAYROLL
Policy name    : POLICY_ON
Policy function: VPDADMIN.POLICY_FUNCTION
RLS view  :
SELECT  "EMPID","DEPT","TOTAL","TAXES" FROM "VPDADMIN"."PAYROLL"   "PAYROLL" WHERE (upper(EMPID)=E3) 
ORA-00904: "E3": invalid identifier

so what is wrong? there is already E3 as empid in both employees and payroll table I do not get what is happening and how to fix it

Answer 1

It appears that the RETURN statement in POLICY_FUNCTION should be

return 'upper(EMPID)=''' || v_id || '''';

EMPID is a character string and must be enclosed in single quotes.

Best of luck.