Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE OR REPLACE type P_REC AS OBJECT
(
    ATTR1    VARCHAR2(64 BYTE),
    ATTR2    VARCHAR2(128 BYTE),
    ATTR3    VARCHAR2(128 BYTE),
    ATTR4    VARCHAR2(128 BYTE)
);
CREATE OR REPLACE type P_REC_LIST is table of P_REC;
create table targetTable 
(
    ATTR1    VARCHAR2(64 BYTE),
    ATTR2    VARCHAR2(128 BYTE),
    ATTR3    VARCHAR2(128 BYTE),
    ATTR4    VARCHAR2(128 BYTE)
);
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'
      WHEN NOT MATCHED THEN
        INSERT (Attr1)
        VALUES (tmp.Attr1);
  END IF;
END testProc;
/
select * from user_errors;
DECLARE
tmpPList P_REC_LIST := P_REC_LIST(P_REC('A1','A2','A3','A4'),
                                  P_REC('B1','B2','B3','B4'),
                                  P_REC('C1','C2','C3','C4') 
                                  );
BEGIN
   testProc(tmpPList);
END;
/
1 rows affected

select * from targetTable;


ATTR1
ATTR2
ATTR3
ATTR4


A1
null
null
null

C1
null
null
null

B1
null
null
null


DECLARE
tmpPList P_REC_LIST := P_REC_LIST(P_REC('A1','A2','A3','A4'),
                                  P_REC('B1','B2','B3','B4'),
                                  P_REC('C1','C2','C3','C4') 
                                  );
BEGIN
   testProc(tmpPList);
END;
/
1 rows affected

select * from targetTable;


ATTR1
ATTR2
ATTR3
ATTR4


A1
A2
null
null

C1
C2
null
null

B1
B2
null
null


DECLARE
tmpPList P_REC_LIST := P_REC_LIST(P_REC('A1','A2','A3','A4'),
                                  P_REC('B1','B2','B3','B4'),
                                  P_REC('D1','D2','D3','D4') 
                                  );
BEGIN
   testProc(tmpPList);
END;
/
1 rows affected

select * from targetTable;


ATTR1
ATTR2
ATTR3
ATTR4


A1
A2
null
null

B1
B2
null
null

D1
null
null
null
ATTR1	ATTR2	ATTR3	ATTR4
A1	null	null	null
C1	null	null	null
B1	null	null	null