Search for a command to run...
I'm a developer that has spent most of his professional career in the Oracle world with extremely brief detours into JAVA, JavaScript, C#, shell scripting and Python.
Current areas of expertise are Oracle APEX, Data Model Design, PL/SQL, Oracle REST with a little performance tuning thrown in.
No comments yet. Be the first to comment.
There are many times when you're coding an application where there is a requirement for seed data that is required for the system to run correctly. If the seed data is changed or missing, some programming functions will fail due to the missing data. ...
This article was first published on blogs.oracle.com in September of 2019. Not long after it was published I was made aware of a couple of security flaws. However by that time the underlying blogging platform Oracle was using had been migrated and I ...
In honor of #JoelKallmanDay, I thought I’d break back into blogging after a long hiatus with an update on some of my more popular posts of the past – Oracle APEX and Workflow. In January of 2018, I kicked off a little personal research project. The g...
After my initial post about this, I had a discussion with John Snyders of HardLikeSoftware fame, and it turns out there is a better and more efficient way to maintain the tree state. While what I outlined definitely works, what follows is the “prefer...
Quite some time ago I was asked by a customer how we could track the changes to a row over time. They had a couple of very important tables for which they wanted to keep track of who changed what.
The interesting thing was that they were not interested in all the columns, only specific, highly important and impactful ones.
At the time, Flashback Data Archive was not an option for them (for reasons I don't remember now) so it was decided that we'd go with a trigger approach to capture the changes for the fields they were interested in.
And true to form, instead of taking the time to write a trigger for each table they wanted to track, I wrote a package that does all the things they would need so that they wouldn't have to think about it as hard next time.
The package has multiple methods including the ability to manage the base objects, including the table into which the audit trail is inserted.
Methods include:
CREATE_AUDIT_TABLE - generates (and optionally executes) the DDL to create the central logging table.
DROP_AUDIT_TABLE - generates (and optionally executes) the DDL to drop the central logging table.
ADD_TABLE_AUDIT_TRIG - generates (and optionally executes) the DDL that will CREATE the trigger used to audit a table.
REMOVE_TABLE_AUDIT_TRIG - generates (and optionally executes) the DDL that will DROP the trigger used to audit a table.
ENABLE_AUDIT_FOR_TABLE - generates (and optionally executes) the DDL that will ENABLE the auditing trigger for a specified table.
DISABLE_AUDIT_FOR_TABLE - generates (and optionally executes) the DDL that will DISABLE the auditing trigger for a specified table.
ENABLE_ALL_AUDIT_TIRGGERS - generates (and optionally executes) the DDL that will ENABLE ALL auditing triggers.
DISABLE_ALL_AUDIT_TRIGGERS - generates (and optionally executes) the DDL that will DISABLE ALL auditing triggers.
REMOVE_AUDIT_RECS_FOR_TABLE - generates (and optionally executes) the DDL that will remove audit records from the central audit table for a specific table being audited.
REMOVE_ALL_AUDIT_RECS - generates (and optionally executes) the DDL that will remove audit records from the central audit table for all table being audited.
TABLE_IS_AUDITED - checks to see if the supplied table name has an audit trigger that conforms to the naming convention used by the package and returns a boolean.
CAPTURE_AUDIT - procedure that is called from the audit triggers to capture and store the changes.
All objects manipulated by this package exist in the current parsing schema.
All triggers that Audit tables will be of the form BUID_<<TABLE_NAME>>_AUD
ANY Triggers that have the above name signature will be affected by this package
Currently, the data types that can be audited are limited to
NUMBER
FLOAT
BINARY_FLOAT
BINARY_DOUBLE
VARCHAR2
CHAR
DATE
TIMESTAMP
TIMESTAMP W TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
CLOB
DataTypes EXCLUDED from audit are:
ROWID
UROWID
BLOB
BFILE
NVARCHAR2
NCHAR
LONG
LONG_RAW
RAW
NOTE: Any varchar2 column greater than 4000 in length will be stored in the audit table's CLOB column rather than in the varchar2 column.
I've made this package available here on GitHub for anyone to use. However, I will say this about using it.
This is probably not the most efficient use of database resources. For a small number of tables and a targeted number of columns, it will likely work just fine, but on a larger scale, you may want to rethink your approach.
For proof of this, you simply need to read Connor McDonald's blog post, A fresh look at auditing row changes.
The current state of the package was aimed at the very specific need of my then-client. There are many things that I would like to do to it to make it more generic and more functional. These changes are outlined in the ISSUES section of the GitHub repository.
As always, your comments and ideas are welcome.