I was dealing with a problem. A new Login was implemented in the database engine and now I have to test the difference between all the tables of two schemas in two servers of oracle.
I connected to one server with sql tool and run the following code.
The work was done.
But this could be done better by using an alternate type and Bulk collect, which I may post in my next post.
Thanks and enjoy.
I connected to one server with sql tool and run the following code.
DECLARE
/**
@author : Rajan Prasad Upadhyay
**/
v_dblink VARCHAR2(200) := '@DBLK_2_QCDBP1';--link to another server
v_owner1 VARCHAR2(200):= 'HF0901001130325';--
v_owner2 VARCHAR2(200):= 'HF0902001130325';--
-----------------------------------------------------
TYPE objectTableCur IS REF CURSOR;
v_ObjectTableCur objectTableCur;
v_query VARCHAR2(2500);
v_tablename VARCHAR2(200);
v_count INT;
BEGIN
v_query := 'SELECT table_name FROM all_tables WHERE owner = '''||v_owner2||''' AND table_name LIKE ''%ABC_%'' ';
OPEN v_objectTableCur FOR v_query;
FETCH v_objectTableCur INTO v_tablename;
LOOP
BEGIN
Dbms_Output.Put_Line(v_tablename);
v_query := 'select count(*) count from (
select * from '||v_tablename||' minus
select * from '||v_owner1||'.'||v_tablename||v_dblink||' )';
--Dbms_Output.Put_Line(v_query);
EXECUTE IMMEDIATE v_query INTO v_count;
Dbms_Output.Put_Line(v_count);
FETCH v_objectTableCur INTO v_tablename;
EXIT WHEN v_objectTableCur%NOTFOUND;
EXCEPTION WHEN OTHERS THEN
Dbms_Output.Put_Line(' SOMETHING IS WRONG WITH THIS.'||Chr(10)||' The datatypes donot match or number of columns are different
or the table is not present in both Tables.');
FETCH v_objectTableCur INTO v_tablename;
NULL;
END;
END LOOP;
END;
The work was done.
But this could be done better by using an alternate type and Bulk collect, which I may post in my next post.
Thanks and enjoy.
