Use Pl/SQL to compare 2 tables

This Script is used to generate Dynamic SQL.

The SQL is used to compare 2 tables using minus function




CREATE OR REPLACE procedure SelTab2Minus
( name_in IN varchar2,table_in IN varchar2 )
IS
stmt varchar2(2000);
col_name VARCHAR2(30);

cursor c1 is
select column_name
from dba_tab_cols
where owner= name_in and table_name = table_in;

BEGIN



open c1;
LOOP
fetch c1 into col_name;
-- DBMS_OUTPUT.PUT_LINE(stmt);
stmt := stmt || col_name ;
EXIT WHEN c1%NOTFOUND;
stmt := stmt ||',';
END LOOP;
close c1;
--DBMS_OUTPUT.PUT_LINE(stmt );
DBMS_OUTPUT.PUT_LINE('select ' || stmt ||' from ' || name_in||'.'||table_in);
--DBMS_OUTPUT.PUT_LINE(' from ' || name_in||'.'||table_in);
DBMS_OUTPUT.PUT_LINE(' minus ');
DBMS_OUTPUT.PUT_LINE('select ' || stmt ||' from ' || name_in||'.'||table_in||'@VASMD_46');

END;
/