Row compare and insert into log table only changed data

Question

I'm trying to compare a global temporary table to another table and want to insert into a log table but can not seem to find the best/most efficient way to accomplish this.

Log Table

CREATE TABLE LogTable
(
  Date_Time    DATETIME,
  Name         VARCHAR2(10 CHAR),
  old          VARCHAR2(20 CHAR),
  new          VARCHAR2(20 CHAR),
)

Object Type

CREATE OR REPLACE type dbo.P_REC AS OBJECT
(
    ATTR1    VARCHAR2(10 CHAR),
    ATTR2    VARCHAR2(20 CHAR),
    ATTR3    VARCHAR2(20 CHAR),
    ATTR4    VARCHAR2(20 CHAR)
);

Collection Type

CREATE OR REPLACE type dbo.P_REC_LIST IS TABLE OF P_REC;

Stored Procedure

PROCEDURE PASSPEOPLETOORACLE(tmpCollection IN P_REC_LIST , resultCursor out sys_refcursor)
IS    
BEGIN

IF tmpCollection .count > 0 THEN

    INSERT INTO tmpPTbl SELECT * FROM table1; <--tmpPTbl is a copy of table1 before the merge statement.

    MERGE INTO table1 MKTP
        USING (
                WITH tmpTBL AS
                (
                    SELECT ADCOLL.ATTR1,
                           ADCOLL.ATTR2,
                           MV.ATTR3,
                           MV.ATTR4
                    FROM TABLE(tmpCollection) ADCOLL
                    LEFT JOIN materializedView MV
                    ON ADCOLL.ATTR1 = MV.ATTR1
                )
                SELECT DISTINCT COALESCE(tmpTBL.ATTR1,MKtmpTBL.ATTR1) AS ATTR1,
                                tmpTBL.ATTR2,
                                tmpTBL.ATTR3,
                                tmpTBL.ATTR4,
                                CASE WHEN tmpTBL.ATTR1 IS NULL
                                     THEN 'Y' ELSE 'N' END
                                                match_flag FROM tmpTBL
                                FULL JOIN table1 MKtmpTBL
                                ON MKtmpTBL.ATTR1 = tmpTBL.ATTR1
               ) usingTBL
        ON (MKTP.ATTR1 = usingTBL.ATTR1)
      WHEN MATCHED THEN
        UPDATE SET MKTP.ATTR2 = usingTBL.ATTR2,
                   MKTP.ATTR3 = usingTBL.ATTR3,
                   MKTP.ATTR4 = usingTBL.ATTR4,
        DELETE WHERE match_flag = 'Y'
      WHEN NOT MATCHED THEN
        INSERT (ATTR1)
        VALUES (usingTBL.ATTR1);

END IF;
END;

Id like a way to compare the newly update records in table1 to the prior records in tmpPTbl and where the old and new values differ, insert a new row into the log table.

2019-02-14 23:59:59,jdoe,abcd,efgh would be an example of a record inserted into the log table. tmpPTbl & table1 both have 50 columns in them & about 16k rows on average.

Thanks XmalevolentX. I mentioned it over on your other post (stackoverflow.com/questions/54695372/…), but in light of this question, it seems like perhaps using an UPDATE ... RETURNING could give you the IDs needed to delete, or to insert to a log table, or both. Thanks — alexgibbs

XING XING · Accepted Answer · 2019-02-15T06:23:18

The best solution for you would be to create a Trigger on table Table1. So that any operation occurs on Table1 it can be logged to Logtable. See below demo:

CREATE TABLE table1  
  (col1 VARCHAR2(10),
   col2 VARCHAR2(10),
  col3 VARCHAR2(10)  );
/
--Trigger
CREATE OR REPLACE TRIGGER Log_Entry before
   INSERT OR
   UPDATE ON table1 FOR EACH row 
BEGIN 

IF INSERTING THEN   
   INSERT INTO LogTable VALUES
    (sysdate, :new.col1, :new.col2, :new.col3
    );
ELSIF  UPDATING THEN

   INSERT INTO LogTable VALUES
    (sysdate, :old.col1, :old.col2, :old.col3
    );
END IF;
END;
/

Execution:

SQL> Insert into table1 values ('A','B','C');       

SQL>Update table1
set col1 ='Z'
where col1 = 'A';    

SQL> Merge INTO table1 tb1 USING
    (SELECT 'Z' col1 , 'D' col2, 'K' col3 FROM dual
    ) tb2 ON (tb1.col1 = tb2.col1)
    WHEN matched THEN
     UPDATE SET tb1.col2=tb2.col2 WHEN NOT matched THEN
    INSERT VALUES
    (tb2.col1,tb2.col2,tb2.col3
    );

SQL>Commit;


SQL> Select * from logtable;

  DATE_TIME NAME       OLD                  NEW
--------- ---------- -------------------- --------------------
15-FEB-19 A          B                    C
15-FEB-19 Z          B                    C
15-FEB-19 Z          B                    C

Note there is no need to copy data to tmpPTbl table as well.

Row compare and insert into log table only changed data

1 Answers