Oracle Merge delete clause where data in target but NOT in source

Question

Trying to delete records from a Target table that are NOT in the source table.

Object Type

CREATE OR REPLACE type dbo.P_REC AS OBJECT
(
    ATTR1    VARCHAR2(64 BYTE),
    ATTR2    VARCHAR2(128 BYTE),
    ATTR3    VARCHAR2(128 BYTE),
    ATTR4    VARCHAR2(128 BYTE)
);

Collection Type

CREATE OR REPLACE type dbo.P_REC_LIST is table of P_REC;

Stored Procedure

PROCEDURE testProc(tmpPList IN P_REC_LIST, resultCursor out sys_refcursor)

IF tmpPList.count > 0 THEN
MERGE INTO [targetTable] TT
        USING (SELECT abc.Attr1 abc.Attr2 FROM TABLE(tmpPList) abc) TMP
        ON (TT.Attr1 = TMP.Attr1)
      WHEN MATCHED THEN
        UPDATE SET TT.Attr2 = TMP.Attr2 
        DELETE WHERE TT.Attr1 NOT IN (SELECT Attr1 FROM TABLE(tmpPList))
      WHEN NOT MATCHED THEN
        INSERT (Attr1)
        VALUES (TMP.Attr1);
END IF;

Currently, I can start from a truncated table and the insert portion works. When run again the update portion works. But when a record is removed from tmpPList, IE, no longer in the source data the record remains in the target table.

Answer 1

In a MERGE delete Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted.

You may FULL JOIN the table with your TMP in the USING query and make use of a flag to delete the unmatched rows.

create or replace  PROCEDURE testProc(tmpPList IN P_REC_LIST)
AS
BEGIN
 IF tmpPList.count > 0 THEN
MERGE INTO targetTable tt
        USING ( with abc as
                 (
                  select * from TABLE(tmpPList)
                  )
                  select COALESCE(abc.attr1,t.attr1) as attr1,
                                     abc.attr2,CASE WHEN abc.attr1 IS NULL
                                           THEN 'Y' ELSE 'N' END 
                                          match_flag FROM abc
                          FULL JOIN targetTable t ON t.attr1 = abc.attr1
               ) tmp
        ON (tt.Attr1 = tmp.attr1)
      WHEN MATCHED THEN
         UPDATE SET tt.Attr2 = tmp.Attr2 
        DELETE WHERE match_flag = 'Y' --This flag to identify the unmatched row
      WHEN NOT MATCHED THEN
        INSERT (Attr1)
        VALUES (tmp.Attr1);
  END IF;
END testProc;
/

Demo