I once built a similar solution, where I also added a "get_changes" pipelined table function that would take a table name and a primary key value, and dynamically do a query on the corresponding audit table, and return a table showing the date of the change, the username, and a text column showing the actual changes to columns/data between each audit row.
This was useful because when all changes are logged, there will be lots and lots of duplicate data, and inspecting this "by hand" in SQL Developer or sqlplus is impractical.
With the suggested function, I could just create a report page in APEX (or any other client) and do a
This has been added in the V2 revision. A JSON array is returned so that it can be massaged however the requester would like via JSON_TABLE and the like.
I once built a similar solution, where I also added a "get_changes" pipelined table function that would take a table name and a primary key value, and dynamically do a query on the corresponding audit table, and return a table showing the date of the change, the username, and a text column showing the actual changes to columns/data between each audit row.
This was useful because when all changes are logged, there will be lots and lots of duplicate data, and inspecting this "by hand" in SQL Developer or sqlplus is impractical.
With the suggested function, I could just create a report page in APEX (or any other client) and do a
select * from table(audit_package.get_changes(p_table_name => 'INVOICE', p_key_column => 'invoice_id', p_id => 123))
and get a list of just the columns that changed (and the old/new value).
I'm attaching an image of what the output could look like, to give you a better idea:
I do have some (old and inefficient!) code I could upload if you are interested in having a look to incorporate something like this.