We have an audit table and a "live row" table which we are inheriting from another system. The audit table only contains a couple of columns:
ISSUE_NO NUMBER(10) NOT NULL,
UPDATE_DT DATE NOT NULL,
CHANGED_BY VARCHAR2(30 BYTE),
OLD_VALUE VARCHAR2(2000 BYTE),
NEW_VALUE VARCHAR2(2000 BYTE),
COLUMN_NAME VARCHAR2(32 BYTE)
The "live table", which contains a cumulative adjustment from all the previous audit table rows, which are all linked together by the issue number. Only one row will ever exist in the "live" table but there can be multiple rows in the audit table and even multiple rows with the same timestamp. (this is because the legacy application saves everything you changed once when you click save) In that instance we could use one row with every change for that time.
We need to figure out a way to get those two tables joined up such that the view or procedure or....that we create makes a detailed row for every audit table row and every active row.
We have not seen any examples out there on 10G (which we are stuck with corporate wide). Surely this isn't rocket science for you expert PL/SQL folks? Or is this something that is just known cannot be done?
Please tell me that this is reasonable to accomplish?