Hello,

If you want to obtain the list of updated columns for a given table, the solution I propose to you is very simple: use an UPDATE trigger with the UPDATING predicate.


=================================================== ===========================================
Definition of the trigger
=================================================== ===========================================
The first step is to define an AFTER UPDATE trigger on the table to be monitored. To identify the updated columns, we will use the IF UPDATING predicate.
The UPDATING predicate (a predicate is a function that returns TRUE or FALSE) has a particularity: it may or may not take a parameter. This parameter is the name of a column, in hard or in the form of a variable and it is Oracle which will say whether or not, this column has been updated. In this variable one positions alternately the name of all the columns of the table, recovered in DBA_TAB_COLUMNS.

The trigger code is as follows.

         create or replace trigger trig_update_col after update on HR.ZZ_TEST01
         DECLARED
           CURSOR c_cursor_column_name IS select column_name from dba_tab_columns where table_name = 'ZZ_TEST01' and owner = 'HR';
           v_name VARCHAR2 (50);
           v_result VARCHAR2 (2000);
           v_count NUMBER;
           v_date TIMESTAMP;

         begin
           v_name: = '';
           v_result: = '';
           v_count: = 0;

           SELECT to_char (SYSDATE, 'DD / MM / YYYY HH24: MI: SS') INTO v_date FROM dual;

           OPEN c_cursor_column_name;

           - retrieving the list of columns from the HR.ZZ_TEST01 table that we are monitoring.
           LOOP
             FETCH c_cursor_column_name INTO v_name;
             EXIT when c_cursor_column_name% NOTFOUND;

             if updating (v_name) then
               - If the column has been updated, then we build a character string listing the names of these columns.
               v_count: = v_count + 1;
               if v_count = 1 then
                 v_result: = v_name;
                        else
                 v_result: = v_result || ',' || v_name;
               end if;
                      end if;
           end loop;

           IF v_count> 0 THEN
             - Insert in the trace table containing a VARCHAR2 variable (4000 CHAR).
             insert into HR.ZZ_EVT values ​​('Columns updated on' || v_date || ':' || v_result || '.');
           END IF;

           CLOSE c_cursor_column_name;

           EXCEPTION
           WHEN OTHERS THEN
             dbms_output.put_line ('CODE ERROR' || TO_CHAR (SQLCODE) || '' || SQLERRM);
         end;

=================================================== ===========================================
Trigger tests
=================================================== ===========================================
The table ZZ_TEST01 contains three columns and we make an

update on one, two or three columns : OBJECT_NAME, OBJECT_TYPE and ID.
         update ZZ_TEST01 set OBJECT_NAME = 'TEST' || to_char (id), OBJECT_TYPE = 'TESTTYPE', id = 8888888 where id = 155;
         update ZZ_TEST01 set OBJECT_NAME = 'TEST' || to_char (id), OBJECT_TYPE = 'TESTTYPE' where id = 244;
         update ZZ_TEST01 set ID = 9999999 where id = 355;

The result meets our needs :-)
Of course, you can add the SQL command, the user who updated the table, etc., to the tracing table, but the principle does not change, use the predicate UPDATING.

         select * from ZZ_EVT;
         LIST_COLS
---------------------------------------------------------- ------------------------------------
          Columns updated on 04/29/17 4:50 p.m .: 55.000000000: OBJECT_NAME, OBJECT_TYPE, ID.
          Columns updated on 4/29/17 4:50 p.m .: 55.000000000: OBJECT_NAME, OBJECT_TYPE.
          Columns updated on 04/29/17 4:50 p.m .: 55.000000000: ID.