You can't have a dynamic record type. Even if you could, how would you know what the field names were?
But what you can do is have a dynamic query to find the differences of the form:
select col1, col2, col3, sum(s), sum(p) from (
select t.*, 1 s, 0 p from s_t t
union all
select t.*, 0 s, 1 p from p_t t
)
group by col1, col2, col3
having sum(s) <> sum(p);
Then
unpivot the results to convert the columns to rows! This then gives you a query with a fixed number of columns. E.g. table name, column name and value:
select decode(ssum, 1, 'S_T', 'P_T' ), col, val
from (
select col1, col2, col3, sum(s) ssum, sum(p) psum from (
select t.*, 1 s, 0 p from s_T t
union all
select t.*, 0 s, 1 p from p_T t
)
group by col1, col2, col3
having sum(s) <> sum(p)
)
unpivot (val for col in ( col1, col2, col3))
You can dynamically generate the column list with listagg():
select listagg(column_name,',') within group (order by column_id)
from user_tab_cols
where table_name = :tab;
So put it together and you can do something like:
create table s_t (
x int, y int, z int
);
create table p_t (
x int, y int, z int
);
insert into s_t values (1, 1, 1);
insert into s_t values (1, 2, 3);
insert into p_t values (1, 1, 1);
insert into p_t values (3, 2, 1);
declare
sql_stmt varchar2(4000);
tab varchar2(30) := 'T';
cols varchar2(1000);
differences_cur sys_refcursor;
tab_name varchar2(30);
col_name varchar2(30);
col_value varchar2(4000);
begin
select listagg(column_name,',') within group (order by column_id)
into cols
from user_tab_cols
where table_name = 'S_' || tab;
sql_stmt := 'select decode(ssum, 1, ''S_' || tab || ''', ''P_' || tab || ''' ), col, val
from (
select ' || cols || ', sum(s) ssum, sum(p) psum from (
select t.*, 1 s, 0 p from s_' || tab || ' t
union all
select t.*, 0 s, 1 p from p_' || tab || ' t
)
group by ' || cols || '
having sum(s) <> sum(p)
)
unpivot (val for col in (x, y, z))';
open differences_cur for sql_stmt;
loop
fetch differences_cur into tab_name, col_name, col_value;
exit when differences_cur%notfound;
dbms_output.put_line (
'Table ' || tab_name || ' is different for ' ||
col_name || ' with ' || col_value
);
end loop;
close differences_cur;
end;
/
Table P_T is different for X with 3
Table P_T is different for Y with 2
Table P_T is different for Z with 1
Table S_T is different for X with 1
Table S_T is different for Y with 2
Table S_T is different for Z with 3
If you want this to only show the fields that are different, you could modify your query to do something like:
- lag the values partitioned by column
- Only return those where the val and the lag value are different
e.g.:
select * from (
select decode(ssum, 1, 'S_T', 'P_T' ), col, val,
lag(val) over (partition by col order by ssum) lgval
from (
select X,Y,Z, sum(s) ssum, sum(p) psum from (
select t.*, 1 s, 0 p from s_T t
union all
select t.*, 0 s, 1 p from p_T t
)
group by X,Y,Z
having sum(s) <> sum(p)
)
unpivot (val for col in (x, y, z))
)
where lgval <> val;
DECODE(SSUM,1,'S_T','P_T') COL VAL LGVAL
S_T X 1 3
S_T Z 3 1