Hi guys,
It is Oracle 19c on Linux 7 x86-64.
I have a simple FGA policy that does not accept MERGE statement.
Here are the code to reproduce the issue:
CREATE TABLE CUSTOMERS2
(CUSTOMER_ID NUMBER(12) PRIMARY KEY,
CUST_FIRST_NAME VARCHAR2(40),
CUST_LAST_NAME VARCHAR2(40)
)
/
INSERT INTO CUSTOMERS2 VALUES (45817,'AUSTIN','CALDWELL');
INSERT INTO CUSTOMERS2 VALUES (45818,'TONY','SIMMONS');
INSERT INTO CUSTOMERS2 VALUES (45819,'JEROME','WARREN');
INSERT INTO CUSTOMERS2 VALUES (45820,'HERBERT','KENNEDY');
INSERT INTO CUSTOMERS2 VALUES (45821,'MALCOLM','DAWSON');
INSERT INTO CUSTOMERS2 VALUES (45822,'JESSE','ROSS');
INSERT INTO CUSTOMERS2 VALUES (45823,'JORGE','HERRERA');
INSERT INTO CUSTOMERS2 VALUES (45824,'MORGAN','AGUIRRE');
INSERT INTO CUSTOMERS2 VALUES (45825,'BRETT','ARMSTRONG');
INSERT INTO CUSTOMERS2 VALUES (45826,'SHAWN','SULLIVAN');
COMMIT;
Create an audit policy on SOE.CUSTOMERS to audit INSERT, UPDATE, and DELETE statements on customers2 for customers whose ID is greater than or equal to 45800
BEGIN
DBMS_FGA.ADD_POLICY(
OBJECT_SCHEMA => 'SOE',
OBJECT_NAME => 'CUSTOMERS2',
POLICY_NAME => 'CUS_POL',
AUDIT_CONDITION => 'CUSTOMER_ID >= 45800',
STATEMENT_TYPES => 'INSERT,UPDATE,DELETE');
END;
/
Execute the following MERGE statement. It should result in inserting a new row in the table.
MERGE INTO CUSTOMERS2 x
USING (SELECT TO_NUMBER('45827') CID, 'ROSE' FNAME, 'SKYLINE' LNAME FROM DUAL) y
ON (x.CUSTOMER_ID = y.CID)
WHEN MATCHED THEN
UPDATE SET x.CUST_FIRST_NAME = y.FNAME,
x.CUST_LAST_NAME = y.LNAME
WHEN NOT MATCHED THEN
INSERT(x.CUSTOMER_ID, x.CUST_FIRST_NAME, x.CUST_LAST_NAME)
VALUES(y.CID, y.FNAME, y.LNAME);
But it returns the following error:
ORA-28138: Error in Policy Predicate
Amazingly, when I try to insert the same row using INSERT statement, it works fine:
SQL> INSERT INTO CUSTOMERS2 SELECT TO_NUMBER('45827') CID, 'ROSE' FNAME, 'SKYLINE' LNAME FROM DUAL ;
1 row created.
Any idea where I went wrong in the MERGE statement?
Thanks in advance!