Generic Trigger to log changes

Hi altogether,

I brainstormed a little bit more to make my approach more generic. The following solution now determines the base table completely dynamic based on the first rowid updated. Further the log table is determined by a mapping function based on the base table. The old values are selected in an autonomous transaction. So the old values represent always the last commited row version. This may be differ from the last version of the row in the local uncommited transaction if multiple updates occur. It is importand to understand that. In my opinion this is not a problem because uncommited versions of the rows updated by local transaction are also not "seen" by anybody else.

I've developed this to show that it is possible to realize a highly generic solution for the use case @mail8mz is faced with based on triggers and some dynamic pl/sql. What do you think about this aproach? Generally I also think that flashback data archive is a better way to do data change logging out of the box. But I'm also a great fan of pl/sql and I like such challenges, irrespective of the question if such aproach is reasonable or not...

CREATE OR REPLACE PACKAGE pkg_log
IS
  --Collection type for the updated row ids
  TYPE coll_row_ids IS TABLE OF VARCHAR2 (18 CHAR);

  --Generic logging method called out of the after statement section of the
  --compound triggers
  PROCEDURE log_old_values (row_ids IN coll_row_ids);
END pkg_log;
/

CREATE OR REPLACE PACKAGE BODY pkg_log
IS
  --Record type for table metadata (owner and name)
  TYPE type_table IS RECORD
  (
   table_owner  all_tables.owner%TYPE,
   table_name   all_tables.table_name%TYPE
  );

  --Determine and return metadata of the base table based on the given rowid
  FUNCTION get_base_table (row_id IN ROWID)
   RETURN type_table
  IS
   v_base_table  type_table;
  BEGIN
   SELECT all_objects.owner, all_objects.object_name
    INTO v_base_table.table_owner, v_base_table.table_name
    FROM all_objects
    WHERE all_objects.object_id =
       dbms_rowid.rowid_object (row_id => get_base_table.row_id);

   RETURN v_base_table;
  END get_base_table;

  --Mapping function to determine the logging table to use based on the given
  --base table
  FUNCTION get_log_table (base_table IN type_table)
   RETURN type_table
  IS
   v_log_table  type_table;
  BEGIN
   v_log_table.table_owner := get_log_table.base_table.table_owner;
   v_log_table.table_name := 'LOG_TABLE';

   RETURN v_log_table;
  END get_log_table;

  --This function returns the old values of the updated rows identified by the
  --given rowids as an opened sys_refcursor. It does this in an autonomous
  --transaction so the old values represent the last commited version of the
  --rows. The structure of the cursor returned is identical to the structure
  --of the log table the data will be written into. Column orders of base and
  --log tables are completely irrelevant. Only columns that exist in the log
  --table are returned. Log metadata columns like LOGTS are filled with the
  --required values, columns which don't exist in the base table are returned
  --with NULL.
  FUNCTION get_old_values (base_table  IN type_table,
              log_table  IN type_table,
              row_ids   IN coll_row_ids)
   RETURN SYS_REFCURSOR
  IS
   --Autonomous transaction to get the last commited version of the rows
   PRAGMA AUTONOMOUS_TRANSACTION;

   v_old_values  SYS_REFCURSOR;
   v_stmt     VARCHAR2 (32767 CHAR);
  BEGIN
   --Create SELECT statement based on the structure of the log table
   SELECT  'SELECT '
       || LISTAGG (
          CASE
           WHEN log_table.column_name = 'LOGTS'
           THEN
             'LOCALTIMESTAMP AS LOGTS'
           ELSE
             NVL2 (base_table.column_name,
                '"' || log_table.column_name || '"',
                'NULL AS "' || log_table.column_name || '"')
          END,
          ',')
        WITHIN GROUP (ORDER BY log_table.column_id ASC)
       || ' FROM "'
       || get_old_values.base_table.table_owner
       || '"."'
       || get_old_values.base_table.table_name
       || '" WHERE rowid IN (SELECT VALUE (rid) FROM TABLE (:1) rid)'
    INTO v_stmt
    FROM all_tab_columns log_table
       LEFT OUTER JOIN all_tab_columns base_table
        ON   base_table.owner =
            get_old_values.base_table.table_owner
          AND base_table.table_name =
            get_old_values.base_table.table_name
          AND base_table.column_name = log_table.column_name
    WHERE   log_table.owner = get_old_values.log_table.table_owner
       AND log_table.table_name = get_old_values.log_table.table_name;


   OPEN v_old_values FOR v_stmt USING get_old_values.row_ids;

   RETURN v_old_values;
  END get_old_values;

  --Generic logging method: The base table is determined by the first rowid of
  --the given collection. The log table is determined by the maping function
  --get_log_table. The old values are returned by the called function
  --get_old_values as a sys_refcursor. The cursor is fetched and the fetched
  --records are written into to log table. This is done in the context of the
  --main transaction.
  PROCEDURE log_old_values (row_ids IN coll_row_ids)
  IS
   v_base_table  type_table;
   v_log_table  type_table;
   v_old_values  SYS_REFCURSOR;
  BEGIN
   IF log_old_values.row_ids.FIRST () IS NOT NULL
   THEN
     v_base_table :=
      get_base_table (
        row_id  =>
         log_old_values.row_ids (log_old_values.row_ids.FIRST ()));
     v_log_table := get_log_table (base_table => v_base_table);
     v_old_values :=
      get_old_values (base_table  => v_base_table,
              log_table  => v_log_table,
              row_ids   => log_old_values.row_ids);

     EXECUTE IMMEDIATE  'DECLARE'
             || '  v_old_values  SYS_REFCURSOR := :1;'
             || '  v_log_record  "'
             || v_log_table.table_owner
             || '"."'
             || v_log_table.table_name
             || '"%ROWTYPE;'
             || 'BEGIN'
             || '  LOOP'
             || '   FETCH v_old_values INTO v_log_record;'
             || '   EXIT WHEN v_old_values%NOTFOUND;'
             || '   INSERT INTO "'
             || v_log_table.table_owner
             || '"."'
             || v_log_table.table_name
             || '"'
             || '      VALUES v_log_record;'
             || '  END LOOP;'
             || 'END;'
      USING IN OUT v_old_values;

     CLOSE v_old_values;
   END IF;
  END log_old_values;
END pkg_log;
/

CREATE OR REPLACE TRIGGER trigger_spfc
  FOR UPDATE
  ON base_table
  COMPOUND TRIGGER
  v_row_ids  pkg_log.coll_row_ids := NEW pkg_log.coll_row_ids ();

  --Collect the updated rowids
  AFTER EACH ROW
  IS
  BEGIN
   v_row_ids.EXTEND (1);
   v_row_ids (v_row_ids.LAST ()) := :old.ROWID;
  END AFTER EACH ROW;

  --Generic logging of the updated rows
  AFTER STATEMENT
  IS
  BEGIN
   pkg_log.log_old_values (row_ids => v_row_ids);
   v_row_ids.delete ();
   v_row_ids := NULL;
  END AFTER STATEMENT;
END trigger_spfc;
/

Best regards

Jan