Table Comparison Function

CREATE OR REPLACE FUNCTION fn_table_comparison (
  table1        VARCHAR2,
  table2        VARCHAR2,
  alias1        VARCHAR2 DEFAULT '1',
  alias2        VARCHAR2 DEFAULT '2'
) RETURN sys_refcursor
AS
  c                 sys_refcursor;
  lv_column_list    VARCHAR2(32000);
BEGIN
  FOR c_col IN (SELECT column_name
                FROM   user_tab_columns
                WHERE  table_name = UPPER(table1)
               )
  LOOP
    lv_column_list := lv_column_list || ', ' || LOWER(c_col.column_name);
  END LOOP;

  OPEN c FOR 'WITH ad AS (' ||
               'SELECT ''' || alias1 || ''' AS src, count(*) AS table_count' || lv_column_list ||
              ' FROM ' || LOWER(table1) ||
              ' GROUP BY ' || SUBSTR(lv_column_list, 3) ||
              ' UNION ALL' ||
              ' SELECT ''' || alias2 || ''' AS src, count(*) AS table_count' || lv_column_list ||
              ' FROM ' || LOWER(table2) ||
              ' GROUP BY ' || SUBSTR(lv_column_list, 3) ||
              ') ' ||
             --'SELECT MAX(src) AS "Table", count(*) "Count", table_count "Table Count"' || lv_column_list ||
             'SELECT MAX(src) AS "Table", table_count "Count"' || lv_column_list ||
            ' FROM ad' ||
            ' GROUP BY table_count' || lv_column_list ||
            ' HAVING count(*) != 2' ||
            ' ORDER BY ' || SUBSTR(lv_column_list, 3) || ', MAX(src)';
  RETURN c;
END fn_table_comparison;
/


--  SELECT fn_table_comparison('test_tab1', 'test_tab2') FROM dual;


variable result refcursor
begin
  :result := fn_table_comparison('test_tab1', 'test_tab2', 'table 1', 'table 2');
end;
/
print result