0

Is there any slick way to create a SQL statement in Oracle to display only fields that have changed? We have a Data table and a related Audit table, with the same field names. I can join them on the proper key fields and then I can check every field individually for changes as:

    SELECT
    t.FIELD1,
    a.FIELD1 AUDIT_FIELD1,
    t.FIELD2,
    a.FIELD2 AUDIT_FIELD2,
    t.FIELD3,
    a.AUDIT_FIELD3,
    etc
    ...
    FROM
    DATA_TABLE t
    INNER JOIN
    AUDIT_TABLE a
    ON
    a.EMP_ID = t.EMP_ID
    ...
    WHERE 
    a.FIELD1 <> t.FIELD1
    OR
    a.FIELD2 <> t.FIELD2
    OR
    a.FIELD3 <> t.FIELD3
    etc etc..

And I wind up with X number of rows where I have at least one changed field per row, but I have to then eyeball all the results to find which of the fields changed.

It seems I should be able to do some kind of query that basically says "For Each Field In The Table If It Doesn't Match The Other Table Display The Field Name and Value."

This all needs to be done in SQL because corporate requires us to use a separate query/reporting tool that can't create any objects (tables, views, triggers, SPs, etc) on the Oracle back-end database.

Steven
  • 13,501
  • 27
  • 102
  • 146
Brian Battles
  • 971
  • 1
  • 7
  • 19

3 Answers3

0

Would something like the following work? It would indicate which fields have changed as a Y/N column for each field, as multiple fields may have changed.

SELECT 
    CASE WHEN a.FIELD1 <> t.FIELD1 THEN 'Y' ELSE 'N' END AS FIELD1CHANGED,
    CASE WHEN a.FIELD2 <> t.FIELD2 THEN 'Y' ELSE 'N' END AS FIELD2CHANGED,
    CASE WHEN a.FIELD3 <> t.FIELD3 THEN 'Y' ELSE 'N' END AS FIELD3CHANGED,
...
Steven
  • 13,501
  • 27
  • 102
  • 146
  • 1
    HI , check "minus" operator which could be useful. http://psoug.org/definition/minus.htm – Ramki Apr 27 '15 at 17:40
  • The CASE WHEN technique works, I've experimented with it, but it still means coding for each specific column and also returning columns that have no changes. Ideally I'd like to just show columns that have been changed...and not have to code each column into the actual SQL, if possible...I was think these could be a way to create some kind of temp table in memory that some way could be queried as "FOR EACH COLUMN IN TEMPTABLE...." but I'm not an advanced enough Oracle SQL coder to know if/how to do something like that. – Brian Battles Apr 27 '15 at 18:00
  • @Brian you could do that using a dynamic query in PL/SQL. But not in plain SQL. – Sylvain Leroux Apr 27 '15 at 18:11
0

A more traditional approach is roll your own auditing. Add a trigger on the table, in pseudo code

 when insert, update or delete for each row

 IF inserting then
 Insert into your_table_audit (fields) Values(:old.id,...,...'INSERT');
 Elsif updating then
 Case
 ..compare all field values to find changed values
 End
 Insert into your_table_audit (fields) Values(:old.id,...,...'Changed '||:old.value||' to '||:new.value);
 Else
 Insert into your_table_audit (fields) Values(:old.id,...,...'DELETE');

this could be encapsulated in a package and with some work applied to any table. You would have to access the names of the columns from Oracle metadata and then use dynamic SQL to construct your code at runtime.

kevinskio
  • 4,431
  • 1
  • 22
  • 36
0

check this could be useful 

create table tab1 (
col1 varchar(10),
col2 varchar(10),
col3 Number(10)
);

insert into Tab1 values ('xxx','xxx',99);
insert into Tab1 values ('aaa','aaa',98);
insert into Tab1 values ('bbb','bbb',97);

create table tab2 (
col1 varchar(10),
col2 varchar(10),
col3 Number(10)
);

insert into Tab2 values ('xxx','xxx',99);
insert into Tab2 values ('aaaA','aaa',98);
insert into Tab2 values ('bbb','bbbB',97);

commit;

select col1,col2,col3 from tab2
minus 
select col1,col2,col3 from tab1

---------- ---------- ----------------------    
COL1       COL2       COL3                   
---------- ---------- ---------------------- 
aaaA       aaa        98                     
bbb        bbbB       97     


--drop table tab1;
--drop table tab2;
Ramki
  • 453
  • 2
  • 7