Skip to Main Content
  • Questions
  • To compare two same tables from different schema without primary key and not same number of columns

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Madhu.

Asked: December 18, 2019 - 10:31 am UTC

Answered by: Chris Saxon - Last updated: December 18, 2019 - 12:02 pm UTC

Category: Database Development - Version: 4.1.3

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Table Functions, Part 3a: table functions as parameterized views in the PL/SQL Challenge website

You Asked

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).

and we said...

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

and you rated our response

  (2 ratings)

Reviews

Need more information

December 18, 2019 - 11:40 am UTC

Reviewer: Madhu K

It would be great if you provide me with the SP and function code for the above question. Is T1 and t2 temp tables to be created? Also when table_name='T2' ,we have to pass the schema name as T2 is in other schema right?. And also T2 not in t1 is required.
Chris Saxon

Followup  

December 18, 2019 - 12:00 pm UTC

It would be great if you followed the question guidelines and gave us DDL for the CUSTOMERS table in your question.

T1 & T2 are just "for example". Modify the query to pass the table names & owners as needed.


Need more information

December 18, 2019 - 11:44 am UTC

Reviewer: Madhu K

And also I cant compare each and every column individually as I am trying to create general SP ,which will compare other tables too. So when input passed with the table name, SP must compare that table with other table.
Chris Saxon

Followup  

December 18, 2019 - 12:02 pm UTC

I'm not sure what the issue is here?

The intersect finds the column names that exist in both tables. You can use this to find the common columns in any two tables. Just update it to use all/dba views and accept owner/table name.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.