oradbanj
Reputation: 563
Oracle Show only Columns that have changed
I have an audit table which has columns prefixed with "old_" and "new_" to indicate old and new values as below:
ID <-- Primary Key
old_cust_name
new_cust_name
old_address
new_address
Does anybody knows of readily available package/procedure which can dynamically generate code to produce report and show column changes, something similar to below:
Example
ID old_name new_name old_address new_address
101 Andru Andrew Main_St Main_St
102 Bill Bill 1st_Av 2nd_Av
103 jack jack High_St High_St
Output
101 name changed : Andru --> Andrew
102 address changed : 1st_Av --> 2nd_Av
103 no change
Upvotes: 1
Views: 114
Answers (2)
Francisco Sitja
Reputation: 1003
Alright, so for the fun of it I created a somewhat convoluted query that will generate a SQL statement to compare old and new values. It figures out the primary key columns and the column list based on Oracle data dictionary and your rule that they start with "NEW_" and "OLD_". Just substitute your schema owner and table_name in the first part of it.
I did not test it thoroughly to the point where I can safely say there may be no bugs or it will not "misbehave". But it seems to be working for this simple test case.
I borrowed from Barbaros Özhan solution as well, so thank you for that.
with tab as (
select owner, table_name
from dba_tables t where t.owner = 'FSITJA' and table_name in ('TEST_COMPARE')),
col_list as (
select co.owner,
co.table_name,
cn.column_name new_col_name,
co.column_name old_col_name,
row_number() over (partition by co.owner, co.table_name order by co.column_name) position,
decode(row_number() over (partition by co.owner, co.table_name order by co.column_name),
count(*) over (partition by co.owner, co.table_name),
'Y',
'N') last_col
from dba_tab_columns cn
join dba_tab_columns co on co.owner = cn.owner
and co.table_name = cn.table_name
and regexp_replace(co.column_name, '^OLD_(.*)$', '\1') = regexp_replace(cn.column_name, '^NEW_(.*)$', '\1')
where (co.owner, co.table_name) in (select owner, table_name from tab)
and regexp_like(cn.column_name, '^NEW_')),
pk_col_list as (
select c.owner,
c.table_name,
cc.column_name,
cc.position,
decode(cc.position, max(cc.position) over (partition by c.owner, c.table_name), 'Y', 'N') last_col
from dba_constraints c
join dba_cons_columns cc on c.owner = cc.owner
and c.table_name = cc.table_name
and c.constraint_name = cc.constraint_name
where (c.owner, c.table_name) in (select owner, table_name from tab)
and c.constraint_type = 'P'
) --
select replace(replace(extract(xmlconcat(xmlelement("xml_part", sql_select),
xmlelement("xml_part", xml_pkcol_list),
xmlelement("xml_part", xml_col_list),
xmlelement("xml_part", from_t)), '/xml_part/text()'),
chr(38)||'apos;', ''''),
chr(38)||'gt;', '>') full_sql
from (select pl.owner,
pl.table_name,
'SELECT ' || chr(10) sql_select,
extract(xmlagg(xmlelement("column_name", ' ' || pl.column_name || ',' || chr(10)) order by pl.position), '/column_name/text()') xml_pkcol_list
from pk_col_list pl
group by pl.owner,
pl.table_name,
'SELECT ' || chr(10)) part1
join (select cl.owner,
cl.table_name,
extract(xmlagg(xmlelement("column_name", ' CASE WHEN nvl(' || cl.old_col_name || ', ' || cl.new_col_name ||
'||''1'') != nvl(' || cl.new_col_name || ', ' || cl.old_col_name || '||''1'')' || chr(10) ||
' THEN ''' || regexp_replace(cl.old_col_name, '^OLD_(.*)$', '\1') ||
' changed: '' || nvl(to_char(' || cl.old_col_name || '), ''''''NULL'''''') || '' --> '' || nvl(to_char(' || cl.new_col_name ||
'), ''''''NULL'''''')' || chr(10) ||
' ELSE ''' || regexp_replace(cl.old_col_name, '^OLD_(.*)$', '\1') ||
' unchanged'' END AS ' || regexp_replace(cl.old_col_name, '^OLD_(.*)$', '\1') ||
decode(cl.last_col, 'N', ',') || chr(10)
) order by cl.position), '/column_name/text()') xml_col_list,
' FROM ' || cl.owner || '.' || cl.table_name || ' t;' from_t
from col_list cl
group by cl.owner,
cl.table_name,
' FROM ' || cl.owner || '.' || cl.table_name || ' t;') part2 on part1.owner = part2.owner and part1.table_name = part2.table_name;
And a sample execution follows:
FSITJA@db01 2019-07-10 13:21:56> create table fsitja.test_compare (id number primary key,
2 new_name varchar2(10),
3 old_name varchar2(10),
4 new_salary number,
5 old_salary number,
6 new_address varchar2(30),
7 old_address varchar2(30));
Table created.
FSITJA@db01 2019-07-10 13:21:56> insert into fsitja.test_compare values (1, 'John', 'James', 1000, 1000, '123 That Road', '332 This Avenue');
1 row created.
FSITJA@db01 2019-07-10 13:21:56> insert into fsitja.test_compare values (2, 'Pat', 'Pat', 1200, 2000, 'None', 'None');
1 row created.
FSITJA@db01 2019-07-10 13:21:56> insert into fsitja.test_compare values (3, 'Jack', null, 5000, 5000, 'None', 'None');
1 row created.
FSITJA@db01 2019-07-10 13:21:56> insert into fsitja.test_compare values (4, 'Dean', 'Dean', null, 900, 'None', 'DIFF ADDRESS');
1 row created.
FSITJA@db01 2019-07-10 13:21:56> insert into fsitja.test_compare values (5, 'Anne', 'Anne', null, null, null, null);
1 row created.
FSITJA@db01 2019-07-10 13:21:56> commit;
Commit complete.
FSITJA@db01 2019-07-10 13:21:56>
FSITJA@db01 2019-07-10 13:21:56> with tab as (
2 select owner, table_name
3 from dba_tables t where t.owner = 'FSITJA' and table_name in ('TEST_COMPARE')),
4 col_list as (
5 select co.owner,
6 co.table_name,
7 cn.column_name new_col_name,
8 co.column_name old_col_name,
9 row_number() over (partition by co.owner, co.table_name order by co.column_name) position,
10 decode(row_number() over (partition by co.owner, co.table_name order by co.column_name),
11 count(*) over (partition by co.owner, co.table_name),
12 'Y',
13 'N') last_col
14 from dba_tab_columns cn
15 join dba_tab_columns co on co.owner = cn.owner
16 and co.table_name = cn.table_name
17 and regexp_replace(co.column_name, '^OLD_(.*)$', '\1') = regexp_replace(cn.column_name, '^NEW_(.*)$', '\1')
18 where (co.owner, co.table_name) in (select owner, table_name from tab)
19 and regexp_like(cn.column_name, '^NEW_')),
20 pk_col_list as (
21 select c.owner,
22 c.table_name,
23 cc.column_name,
24 cc.position,
25 decode(cc.position, max(cc.position) over (partition by c.owner, c.table_name), 'Y', 'N') last_col
26 from dba_constraints c
27 join dba_cons_columns cc on c.owner = cc.owner
28 and c.table_name = cc.table_name
29 and c.constraint_name = cc.constraint_name
30 where (c.owner, c.table_name) in (select owner, table_name from tab)
31 and c.constraint_type = 'P'
32 ) --
33 select replace(replace(extract(xmlconcat(xmlelement("xml_part", sql_select),
34 xmlelement("xml_part", xml_pkcol_list),
35 xmlelement("xml_part", xml_col_list),
36 xmlelement("xml_part", from_t)), '/xml_part/text()'),
37 chr(38)||'apos;', ''''),
38 chr(38)||'gt;', '>') full_sql
39 from (select pl.owner,
40 pl.table_name,
41 'SELECT ' || chr(10) sql_select,
42 extract(xmlagg(xmlelement("column_name", ' ' || pl.column_name || ',' || chr(10)) order by pl.position), '/column_name/text()') xml_pkcol_list
43 from pk_col_list pl
44 group by pl.owner,
45 pl.table_name,
46 'SELECT ' || chr(10)) part1
47 join (select cl.owner,
48 cl.table_name,
49 extract(xmlagg(xmlelement("column_name", ' CASE WHEN nvl(' || cl.old_col_name || ', ' || cl.new_col_name ||
50 '||''1'') != nvl(' || cl.new_col_name || ', ' || cl.old_col_name || '||''1'')' || chr(10) ||
51 ' THEN ''' || regexp_replace(cl.old_col_name, '^OLD_(.*)$', '\1') ||
52 ' changed: '' || nvl(to_char(' || cl.old_col_name || '), ''''''NULL'''''') || '' --> '' || nvl(to_char(' || cl.new_col_name ||
53 '), ''''''NULL'''''')' || chr(10) ||
54 ' ELSE ''' || regexp_replace(cl.old_col_name, '^OLD_(.*)$', '\1') ||
55 ' unchanged'' END AS ' || regexp_replace(cl.old_col_name, '^OLD_(.*)$', '\1') ||
56 decode(cl.last_col, 'N', ',') || chr(10)
57 ) order by cl.position), '/column_name/text()') xml_col_list,
58 ' FROM ' || cl.owner || '.' || cl.table_name || ' t;' from_t
59 from col_list cl
60 group by cl.owner,
61 cl.table_name,
62 ' FROM ' || cl.owner || '.' || cl.table_name || ' t;') part2 on part1.owner = part2.owner and part1.table_name = part2.table_name;
FULL_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
SELECT
ID,
CASE WHEN nvl(OLD_ADDRESS, NEW_ADDRESS||'1') != nvl(NEW_ADDRESS, OLD_ADDRESS||'1')
THEN 'ADDRESS changed: ' || nvl(to_char(OLD_ADDRESS), '''NULL''') || ' --> ' || nvl(to_char(NEW_ADDRESS), '''NULL''')
ELSE 'ADDRESS unchanged' END AS ADDRESS,
CASE WHEN nvl(OLD_NAME, NEW_NAME||'1') != nvl(NEW_NAME, OLD_NAME||'1')
THEN 'NAME changed: ' || nvl(to_char(OLD_NAME), '''NULL''') || ' --> ' || nvl(to_char(NEW_NAME), '''NULL''')
ELSE 'NAME unchanged' END AS NAME,
CASE WHEN nvl(OLD_SALARY, NEW_SALARY||'1') != nvl(NEW_SALARY, OLD_SALARY||'1')
THEN 'SALARY changed: ' || nvl(to_char(OLD_SALARY), '''NULL''') || ' --> ' || nvl(to_char(NEW_SALARY), '''NULL''')
ELSE 'SALARY unchanged' END AS SALARY
FROM FSITJA.TEST_COMPARE t;
FSITJA@db01 2019-07-10 13:21:59> SELECT
2 ID,
3 CASE WHEN nvl(OLD_ADDRESS, NEW_ADDRESS||'1') != nvl(NEW_ADDRESS, OLD_ADDRESS||'1')
4 THEN 'ADDRESS changed: ' || nvl(to_char(OLD_ADDRESS), '''NULL''') || ' --> ' || nvl(to_char(NEW_ADDRESS), '''NULL''')
5 ELSE 'ADDRESS unchanged' END AS ADDRESS,
6 CASE WHEN nvl(OLD_NAME, NEW_NAME||'1') != nvl(NEW_NAME, OLD_NAME||'1')
7 THEN 'NAME changed: ' || nvl(to_char(OLD_NAME), '''NULL''') || ' --> ' || nvl(to_char(NEW_NAME), '''NULL''')
8 ELSE 'NAME unchanged' END AS NAME,
9 CASE WHEN nvl(OLD_SALARY, NEW_SALARY||'1') != nvl(NEW_SALARY, OLD_SALARY||'1')
10 THEN 'SALARY changed: ' || nvl(to_char(OLD_SALARY), '''NULL''') || ' --> ' || nvl(to_char(NEW_SALARY), '''NULL''')
11 ELSE 'SALARY unchanged' END AS SALARY
12 FROM FSITJA.TEST_COMPARE t;
ID ADDRESS NAME SALARY
---- -------------------------------------------------- ------------------------------ ------------------------------
1 ADDRESS changed: 332 This Avenue --> 123 That Road NAME changed: James --> John SALARY unchanged
2 ADDRESS unchanged NAME unchanged SALARY changed: 2000 --> 1200
3 ADDRESS unchanged NAME changed: 'NULL' --> Jack SALARY unchanged
4 ADDRESS changed: DIFF ADDRESS --> None NAME unchanged SALARY changed: 900 --> 'NULL'
5 ADDRESS unchanged NAME unchanged SALARY unchanged
FSITJA@db01 2019-07-10 13:22:06>
Hope it helps. Please do let me know if you find issues in it.
Francisco.
Upvotes: 0
Barbaros Özhan
Reputation: 65105
You can use a case..when expression as
select case
when nvl(old_name,new_name||'x') != nvl(new_name,old_name||'x') then
ID||' name changed : '||old_name||' --> '||new_name
when nvl(old_address,new_address||'x') != nvl(new_address,old_address||'x') then
ID||' address changed : '||old_address||' --> '||new_address
else
ID||' no change'
end as "Output"
from t;
Upvotes: 1
Related Questions
- Select data which was changed once
- ORACLE: SQL; Select a distinct records if and only if one column changed
- query to retrieve changes in multiple columns in sql
- List The Rows Where a Column Value Changes
- SQL: Return only rows with changes in a field
- oracle identify every change of a column
- How to SELECT only value changes in Oracle?
- Oracle SQL -- Find the most recent data where a column changed in a history table
- How to know a row of a table has changed?
- A query which shows changes of a column from a table