Using Oracle Enterprise Manager for managing VPD

Next, we will create a policy that will be applied on UPDATE statements that will ensure that the salaries of employees who currently make less than 3000 USD and who do not earn a commission will receive an additional 1500 USD raise when the UPDATE statement is executed.

Getting ready

All steps will be performed on the HACKDB database.

How to do it...

1. Connect as HR and create a new table named employees_test_vpd, based on the employees table as follows:

   SQL> conn HR
   Enter password:
   Connected.
   SQL> create table employees_test_vpd as select * from employees where salary is
   not null and commission_pct is null;
   
   Table created.
   

2. Connect as system and create the SALRISE_POL_FUNC policy function defined on the UPDATE statement as follows:

   SQL> conn system
   Enter password:
   Connected.
   SQL> CREATE OR REPLACE
     FUNCTION salrise_pol_func
       (
         schema_v IN VARCHAR2,
         tbl_v VARCHAR2)
       RETURN VARCHAR2
     IS
       ret_val VARCHAR2(200);
   ...