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.
Connect as
HR
and create a new table namedemployees_test_vpd
, based on theemployees
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.
Connect as
system
and create theSALRISE_POL_FUNC
policy function defined on theUPDATE
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); ...