We have table 'CUSTOMER' in two different schema's. Both are not having any primary key and the column numbers in both table do not match(i.e schema1 table can have 97 column other schema table has 101).the column names are same which are present in both tables. My task is to compare these 2 tables.
Firstly, For this i have created function which will fetch common columns in both tables.
create or replace function getcolumn(scheam1 varchar2,table1 varchar2) return sys_refcursor
as lc_refcursor
begin open lc_refcursor for
select c1.column_name from sys.all_tab_columns c1 inner join user_tab_columns c2 on c1.column_name=c2.column_name and c1.table_name=c2.table_name where c1.owner=scheam1 and c1.table_name=table1 ;
return lc_refcursor;
end;
Then i would be calling this into a procedure , using this columnlist we can compare two tables. (I am hoping the algorithm is correct, if not please suggest algorithm to compare tables)
create or replace procedure comparetable( schema1 in varchar2,table1 in varchar2,schema2 in varchar2,table2 in varchar2, p_array_size in number default 100 )
as l_cursor sys_refcursor;
l_owner dbms_sql.varchar2_table;
l_object_name dbms_sql.varchar2_table;
l_object_id dbms_sql.number_table;
l_created dbms_sql.date_table;
l_processed_cnt number := 0;
column_list varchar2(3000);begin
column_list := 'select * from getcolumn('||schema1||','||table1||'))';
open l_cursor for 'select t1.* from (select distinct'||column_list||'from'||table1||')t1 where not exists(select 1 from'||schema2||'.'||table2||'t2 where (t1.'||column_list||'=t2.'||column_list||'or t1.||column_list||'is null and t2.'||column_list|| is null))';
loop
fetch l_cursor BULK COLLECT
into l_owner, l_object_name, l_object_id, l_created
LIMIT p_array_size;
for i in 1 .. l_owner.count
loop
l_processed_cnt := l_processed_cnt+1;
end loop;
exit when l_cursor%notfound;
end loop;
close l_cursor;
dbms_output.put_line( 'processed ' || l_processed_cnt || ' records' );
end;
This procedure is giving error as missing expression at line 13.
Please help with the error and also the algorithm to compare tables depending on the record loaded date(i havent passed this input yet).
First up:
You're using string concatenation. So SQL injection is a risk. Make sure you validate the values!
Next:
column_list := 'select * from getcolumn('||schema1||','||table1||'))';
Getcolumn returns a refcursor. You can't use this in the from clause of a query. You'd have to change it to a table function.
Or do something like this:
create table t1 (
c1 int,
c2 int,
c3 int
);
create table t2 (
c2 int,
c3 int,
c4 int
);
with cols as (
select column_name from user_tab_cols
where table_name = 'T1'
intersect
select column_name from user_tab_cols
where table_name = 'T2'
)
select listagg ( column_name, ',' )
within group ( order by column_name )
from cols;
LISTAGG(COLUMN_NAME,',')WITHINGROUP(ORDERBYCOLUMN_NAME)
C2,C3
And include the result of that in your column comparison.
Finally:
The comparison only returns rows from T1 without a match in T2. What about rows in T2 not in T1? Do you need these too?
Also this:
t1.'||column_list||'=t2.'||column_list
Ain't gonna work. You need to compare each column individually.
Check these answers on comparing tables for other ways to do this:
https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-compare-two-tables-of-data https://asktom.oracle.com/pls/apex/f%3Fp%3D100:11:0::::P11_QUESTION_ID:1004115105172