create table employees_vpd
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)  CONSTRAINT     emp_last_name_nn  NOT NULL
    , salary         NUMBER(8,2)
    , department_id  NUMBER(4)
    );        
create table departments_vpd
   ( d_name         VARCHAR2(100)
   , department_id  NUMBER(4))
;      
create table vpd_test(text_msg varchar2(100))
;
insert into departments_vpd values('HR', 10)
;
insert into employees_vpd values(100,'Jim','Clarke',5000,101)
;
create or replace view emp_dept as 
select first_name, last_name, sum(salary) salary, d_name
  from employees_vpd e, departments_vpd d
 where e.department_id = d.department_id
 group by first_name, last_name, d_name
 order by last_name
 ; 
create or replace trigger tr_emp_dept_bur
instead of update on emp_dept 
for each row
declare
begin
  insert into vpd_test values('in update');
end; 
/
create or replace package pkg_vpd_test as
  function fn_vpd_where_clause(object_owner VARCHAR2 DEFAULT 'QUO_SYS',
                               object_name  VARCHAR2 DEFAULT NULL)
    return varchar2;
end pkg_vpd_test;
/
create or replace package body pkg_vpd_test as
  function fn_vpd_where_clause(object_owner VARCHAR2 DEFAULT 'QUO_SYS',
                               object_name  VARCHAR2 DEFAULT NULL)  
   return varchar2 is
  begin
    return '(1 = 2)';
  end;
end pkg_vpd_test;
/
begin
  dbms_rls.add_policy(object_schema   => 'QUO_SYS', --need to be changed
                      object_name     => 'EMP_DEPT',
                      policy_name     => 'POL_FN_VPD_WHERE_CLAUSE_S',
                      function_schema => 'QUO_SYS', --need to be changed
                      policy_function => 'PKG_VPD_TEST.FN_VPD_WHERE_CLAUSE',
                      statement_types => 'SELECT',
                      update_check    => TRUE);
end;
/
begin
  dbms_rls.add_policy(object_schema   => 'QUO_SYS', --need to be changed
                      object_name     => 'EMP_DEPT',
                      policy_name     => 'POL_FN_VPD_WHERE_CLAUSE_U',
                      function_schema => 'QUO_SYS', --need to be changed
                      policy_function => 'PKG_VPD_TEST.FN_VPD_WHERE_CLAUSE',
                      statement_types => 'UPDATE',
                      update_check    => TRUE);
end;
/
update emp_dept set first_name = 'Tom' --it is updating one row though policy returns 1 = 2
;
--if I test the predicte using the below query, predicate is getting generated fine, but it is not taking the effect 
select sql_text, predicate, policy, object_name, s.sql_id, last_load_time 
  from v$sqlarea s, v$vpd_policy v
 where hash_value = sql_hash
 order by s.last_load_time desc 
 ; 
select * from vpd_test --test table inside the instead of trigger
;
select * from emp_dept --it works fine, it is not returning any data
; 

create table employees_vpd
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)  CONSTRAINT     emp_last_name_nn  NOT NULL
    , salary         NUMBER(8,2)
    , department_id  NUMBER(4)
    );        
create table departments_vpd
   ( d_name         VARCHAR2(100)
   , department_id  NUMBER(4))
;      
create table vpd_test(text_msg varchar2(100))
;
insert into departments_vpd values('HR', 10)
;
insert into employees_vpd values(100,'Jim','Clarke',5000,10)
;
create or replace view emp_dept as 
select first_name, last_name, sum(salary) salary, d_name
  from employees_vpd e, departments_vpd d
 where e.department_id = d.department_id
 group by first_name, last_name, d_name
 order by last_name
 ; 
create or replace trigger tr_emp_dept_bur
instead of update on emp_dept 
for each row
declare
begin
  insert into vpd_test values('in update');
end; 
/
create or replace package pkg_vpd_test as
  function fn_vpd_where_clause(object_owner VARCHAR2 DEFAULT 'QUO_SYS',
                               object_name  VARCHAR2 DEFAULT NULL)
    return varchar2;
end pkg_vpd_test;
/
create or replace package body pkg_vpd_test as
  function fn_vpd_where_clause(object_owner VARCHAR2 DEFAULT 'QUO_SYS',
                               object_name  VARCHAR2 DEFAULT NULL)  
   return varchar2 is
  begin
    return '(1 = 2)';
  end;
end pkg_vpd_test;
/
begin
  dbms_rls.add_policy(object_schema   => 'QUO_SYS', --need to be changed
                      object_name     => 'EMP_DEPT',
                      policy_name     => 'POL_FN_VPD_WHERE_CLAUSE_S',
                      function_schema => 'QUO_SYS', --need to be changed
                      policy_function => 'PKG_VPD_TEST.FN_VPD_WHERE_CLAUSE',
                      statement_types => 'SELECT',
                      update_check    => TRUE);
end;
/
begin
  dbms_rls.add_policy(object_schema   => 'QUO_SYS', --need to be changed
                      object_name     => 'EMP_DEPT',
                      policy_name     => 'POL_FN_VPD_WHERE_CLAUSE_U',
                      function_schema => 'QUO_SYS', --need to be changed
                      policy_function => 'PKG_VPD_TEST.FN_VPD_WHERE_CLAUSE',
                      statement_types => 'UPDATE',
                      update_check    => TRUE);
end;
/
update emp_dept set first_name = 'Tom' --it is updating one row though policy returns 1 = 2
;
--if I test the predicte using the below query, predicate is getting generated fine, but it is not taking the effect 
select sql_text, predicate, policy, object_name, s.sql_id, last_load_time 
  from v$sqlarea s, v$vpd_policy v
 where hash_value = sql_hash
 order by s.last_load_time desc 
 ; 
select * from vpd_test --test table inside the instead of trigger
;
select * from emp_dept --it works fine, it is not returning any data
;