Compare a row between 2 tables
I’ve been working on our licensing application and there’s a requirement to store quotes for changes to server details. A quote is basially an update to the server details info but it’s stored in a separate table, the actual server details are not updated. Essentially our quote table is a copy of the server table with a couple of additional columns. For simplicity on the data entry page I use two “Automatic Row Processing (DML)” processes. One is responsible for maintaining the server table and the other is for adding entries to the quote table. I’m able to use 2 of these processes because they’ve got the same table structure… the only trick is that my Quote button has a Database Action: “SQL Insert action” because each quote is a new record.
Now the problem I had was that our sales team wanted to be notified via email of the data changes, i.e. only the fields that have changed… and they wanted to know what they were originally and what they are now. I started to scratch my head how I could easily do this, so after 5 mins searching on good ol Google I came across the following article by Kevin Meade: http://www.orafaq.com/node/1826
Now it didn’t quite do what I needed, as it compares data in the same table, so i made a number of modifications to it and here’s the raw end result (might have the odd bug or two, as I haven’t tested the column exclusions).
create or replace type o_column_value_difference is object
( column_name varchar2(30)
, original_value varchar2(4000)
, new_value varchar2(4000)
)
/
create or replace type c_column_value_difference is table of o_column_value_difference
/
-- ---------------------------------------------------------------------------
--
-- Package : ola_diff_utils
-- Author : Matt Nolan
-- Description : This is a package for comparing the differences between two
-- rows from the same or different tables
-- (assuming the table structure is the same)
--
-- Note: this code is a modified version posted by Kevin Meade
-- http://www.orafaq.com/node/1826
--
-- ---------------------------------------------------------------------------
-- Revision History
-- Date Author Reason for Change
-- ---------------------------------------------------------------------------
-- 05 FEB 2010 M.Nolan Created.
-- ---------------------------------------------------------------------------
FUNCTION show_column_diffs
( p_owner IN VARCHAR2
, p_a_table_name IN VARCHAR2
, p_a_rowid IN ROWID
, p_a_excluded_columns_list IN VARCHAR2
, p_b_table_name IN VARCHAR2
, p_b_rowid IN ROWID
, p_b_excluded_columns_list IN VARCHAR2
) RETURN c_column_value_difference
IS
v_where_string_1 VARCHAR2(4000);
v_where_string_2 VARCHAR2(4000);
sql_v VARCHAR2(32000);
col_expression_v VARCHAR2(32000);
c_column_value_difference_v c_column_value_difference := c_column_value_difference();
c_column_value_difference_f_v c_column_value_difference := c_column_value_difference();
BEGIN
v_where_string_1 := 'a.'||'rowid='''||p_a_rowid||'''';
v_where_string_2 := 'b.'||'rowid='''||p_b_rowid||'''';
FOR i IN 1..50 LOOP
sql_v := NULL;
FOR r1 IN
( SELECT column_name
, data_type
FROM all_tab_columns
WHERE owner = p_owner
AND table_name = p_a_table_name
AND data_type in ('DATE','NUMBER','VARCHAR2','CHAR')
AND instr(','||upper(p_a_excluded_columns_list)||',',','||column_name||',') = 0
AND column_id BETWEEN (i-1)*10+1 AND (i*10)
ORDER BY column_name
) LOOP
IF r1.data_type in ('NUMBER','VARCHAR2','CHAR') THEN
col_expression_v := ''''||r1.column_name||''''||' column_name,substr(a.'||lower(r1.column_name)||',1,4000) orignal_value, substr(b.'||lower(r1.column_name)||',1,4000) new_value';
ELSE
col_expression_v := ''''||r1.column_name||''''||' column_name,substr(nvl(to_char(a.'||lower(r1.column_name)||',''dd-mon-rrrr hh24:mi:ss''),lpad('' '',20,'' '')),1,4000) original_value, substr(nvl(to_char(b.'||lower(r1.column_name)||',''dd-mon-rrrr hh24:mi:ss''),lpad('' '',20,'' '')),1,4000) new_value';
END IF;
sql_v := sql_v||' UNION ALL SELECT '||col_expression_v||' FROM '||p_owner||'.'||p_a_table_name||' a,'||p_owner||'.'||p_b_table_name||' b where '||v_where_string_1||' and '||v_where_string_2||' and decode(a.'||r1.column_name||',b.'||r1.column_name||',0,1) = 1';
END LOOP;
IF sql_v IS NOT NULL THEN
sql_v := 'SELECT CAST(MULTISET(SELECT * FROM ( '||substr(sql_v,11)||' )) AS c_column_value_difference ) FROM dual';
EXECUTE IMMEDIATE sql_v INTO c_column_value_difference_v;
SELECT CAST(MULTISET(SELECT * FROM(
SELECT *
FROM TABLE(CAST(c_column_value_difference_v AS c_column_value_difference))
UNION ALL
SELECT *
FROM TABLE(CAST(c_column_value_difference_f_v AS c_column_value_difference))
)) AS c_column_value_difference)
INTO c_column_value_difference_f_v
FROM dual
;
END IF;
END LOOP;
IF c_column_value_difference_f_v.last IS NULL THEN
c_column_value_difference_f_v.extend;
c_column_value_difference_f_v(c_column_value_difference_f_v.last) := o_column_value_difference(null,null,null);
END IF;
RETURN (c_column_value_difference_f_v);
EXCEPTION
WHEN OTHERS THEN
-- Something here
RAISE;
END show_column_diffs;
Note: p_b_table_name can have additional columns to p_a_table_name, i.e. be a superset, but it must have all the columns from p_a_table_name as a minimum otherwise the code will error.
And here’s an example of how you use it in SQL
select c.*
from (
select max(rowid) b_rowid
from OLA_COMPANY_DB_INST_QUOTES
where instance_id = 168509198945914487559460687942923611700
) b,
(
select rowid a_rowid
from OLA_COMPANY_DB_INSTANCES
where instance_id = 168509198945914487559460687942923611700
) a,
table(cast(ola_diff_utils.show_column_diffs
( user
, 'OLA_COMPANY_DB_INSTANCES'
, a_rowid
, NULL
, 'OLA_COMPANY_DB_INST_QUOTES'
, b_rowid
, NULL) as c_column_value_difference)
) c
order by 1,2
and here’s the output of the above query
| COLUMN_NAME | ORIGINAL_VALUE | NEW_VALUE |
|---|---|---|
| UPDATED_ON | 04-feb-2010 12:44:09 | 05-feb-2010 16:12:56 |
| PRODUCT_ID | Standard Edition | Enterprise Edition |
I hope you find it useful…..
