Can we perform Merge like operation in bulk?

We have a procedure which receives 200 records in a custom type. Using a For Loop it inserts those records in a Table XYZ.

(Unfortunately This XYZ table is not having any primary key and it has around 6.5 million records). Now once the insert is done a Delete Operation is executed.

Existing Code:-

PROCEDURE XXX_FIN_INSERT_DATA_PRC_YYY_AP (p_in_YYY_AP XXX_FIN_YYY_AP_TYPE_V2) IS
  test_gl_t XXX_FIN_YYY_AP_TYPE_V2;
  BEGIN
  test_gl_t := p_in_YYY_AP;

  FOR i IN test_gl_t.FIRST .. test_gl_t.LAST
  LOOP
    INSERT
    INTO XXX_FIN_YYY_AP_TBL(
          DOC_TYPE          
        ,VENDOR_NAME       
        ,SUPPLIER_SEGMENT1 
        ,INVOICE_NUMBER    
        ,INVOICE_DATE        
        ,INVOICE_AMOUNT    
        ,COST_CENTER       
        ,PO_HEADER_SEGMENT1
        ,CHECK_NUMBER      
        ,CHECK_DATE        
        ,CHECK_AMOUNT      
        ,INVOICE_ID        
        ,CHECK_ID          
        ,DIST_AMOUNT       
        ,FILE_ID           
        ,XXX_RECORD_ID     )
      VALUES
      (
        test_gl_t (i).DOC_TYPE          
        ,test_gl_t (i).VENDOR_NAME       
        ,test_gl_t (i).SUPPLIER_SEGMENT1 
        ,test_gl_t (i).INVOICE_NUMBER    
        ,test_gl_t (i).INVOICE_DATE      
        ,test_gl_t (i).INVOICE_AMOUNT    
        ,test_gl_t (i).COST_CENTER       
        ,test_gl_t (i).PO_HEADER_SEGMENT1
        ,test_gl_t (i).CHECK_NUMBER      
        ,test_gl_t (i).CHECK_DATE        
        ,test_gl_t (i).CHECK_AMOUNT      
        ,test_gl_t (i).INVOICE_ID        
        ,test_gl_t (i).CHECK_ID          
        ,test_gl_t (i).DIST_AMOUNT       
        ,test_gl_t (i).FILE_ID 
        ,XXX_FIN_JOURNALS_REC_SEQ.NEXTVAL);

    DELETE FROM XXX_FIN_YYY_AP_TBL WHERE 
    DOC_TYPE = test_gl_t (i).DOC_TYPE
    AND VENDOR_NAME = test_gl_t (i).VENDOR_NAME
    AND SUPPLIER_SEGMENT1 = test_gl_t (i).SUPPLIER_SEGMENT1 
    AND INVOICE_NUMBER = test_gl_t (i).INVOICE_NUMBER
    AND INVOICE_DATE = test_gl_t (i).INVOICE_DATE   
    AND COST_CENTER = test_gl_t (i).COST_CENTER 
    AND CHECK_NUMBER = test_gl_t (i).CHECK_NUMBER
    AND CHECK_DATE = test_gl_t (i).CHECK_DATE
    AND INVOICE_ID = test_gl_t (i).INVOICE_ID
    AND CHECK_ID = test_gl_t (i).CHECK_ID 
    AND FILE_ID < test_gl_t (i).FILE_ID;

    END LOOP;
  END XXX_FIN_INSERT_DATA_PRC_YYY_AP;

I tried modifying this to :-

PROCEDURE XXX_FIN_INSERT_DATA_PRC_YYY_AP_NEW (p_in_YYY_AP XXX_FIN_YYY_AP_TYPE_V2)
     IS
  test_gl_t XXX_FIN_YYY_AP_TYPE_V2;
  TYPE temp_table_type is TABLE of XXX_FIN_YYY_AP_TBL%ROWTYPE INDEX BY PLS_INTEGER;
  temp_table temp_table_type;
  CURSOR c is SELECT * FROM XXX_FIN_YYY_AP_TBL ORDER BY INVOICE_ID DESC;
  
  BEGIN
  test_gl_t := p_in_YYY_AP;
  OPEN c;
  FETCH c BULK COLLECT INTO temp_table LIMIT 5000; 
    LOOP
        FOR idx IN 1..temp_table.LAST LOOP
            FOR i IN test_gl_t.FIRST .. test_gl_t.LAST LOOP
                    IF (    temp_table(idx).DOC_TYPE = test_gl_t(i).DOC_TYPE
                        AND temp_table(idx).VENDOR_NAME = test_gl_t(i).VENDOR_NAME
                        AND temp_table(idx).SUPPLIER_SEGMENT1 = test_gl_t(i).SUPPLIER_SEGMENT1 
                        AND temp_table(idx).INVOICE_NUMBER = test_gl_t(i).INVOICE_NUMBER
                        AND temp_table(idx).INVOICE_DATE = test_gl_t(i).INVOICE_DATE    
                        AND temp_table(idx).COST_CENTER = test_gl_t(i).COST_CENTER 
                        AND temp_table(idx).CHECK_NUMBER = test_gl_t(i).CHECK_NUMBER
                        AND temp_table(idx).CHECK_DATE = test_gl_t(i).CHECK_DATE
                        AND temp_table(idx).INVOICE_ID = test_gl_t(i).INVOICE_ID
                        AND temp_table(idx).CHECK_ID = test_gl_t(i).CHECK_ID 
                        AND temp_table(idx).FILE_ID < test_gl_t(i).FILE_ID) THEN
                        
                        INSERT INTO XXX_FIN_YYY_AP_TBL (
                                DOC_TYPE,VENDOR_NAME,SUPPLIER_SEGMENT1,INVOICE_NUMBER,INVOICE_DATE,INVOICE_AMOUNT
                                ,COST_CENTER,PO_HEADER_SEGMENT1,CHECK_NUMBER,CHECK_DATE,CHECK_AMOUNT,INVOICE_ID
                                ,CHECK_ID,DIST_AMOUNT,FILE_ID,XXX_RECORD_ID
                                )
                        VALUES
                        (
                            test_gl_t(i).DOC_TYPE          
                            ,test_gl_t(i).VENDOR_NAME       
                            ,test_gl_t(i).SUPPLIER_SEGMENT1 
                            ,test_gl_t(i).INVOICE_NUMBER    
                            ,test_gl_t(i).INVOICE_DATE       
                            ,test_gl_t(i).INVOICE_AMOUNT    
                            ,test_gl_t(i).COST_CENTER       
                            ,test_gl_t(i).PO_HEADER_SEGMENT1
                            ,test_gl_t(i).CHECK_NUMBER      
                            ,test_gl_t(i).CHECK_DATE        
                            ,test_gl_t(i).CHECK_AMOUNT      
                            ,test_gl_t(i).INVOICE_ID        
                            ,test_gl_t(i).CHECK_ID          
                            ,test_gl_t(i).DIST_AMOUNT       
                            ,test_gl_t(i).FILE_ID 
                            ,XXX_FIN_JOURNALS_REC_SEQ.NEXTVAL
      );
            END IF;     
            END LOOP;
        END LOOP;
  EXIT WHEN c%notfound;
  END LOOP;
  CLOSE c;
  
    
  END XXX_FIN_INSERT_DATA_PRC_YYY_AP_NEW;

I ran the main service which invokes My procedure. The Service hanged. Now When I added debug statements in my code and I tried compiling the SQL Developer hanged. I restarted many times but I am unable to revert.

I requested DBA's help on this.

Now my question to experts are :-

• What is wrong with the code I wrote? Is there a better approach?
• I have indexed the table too but I wanted to know if there is a better solution.

Any suggestion / discussion / tips will be helpful.

Regards, Deb