Multiset operations with nested tables

Daily quizzes provided by www.plsqlchallenge.com are the right way to learn PL/SQL language, to know about many features, tips and trics. The site also allows to communicate with developers and learn the best practices.

PL/SQL Challenge suggested interesting task yesterday - to develop code "to compare row counts of all tables in two schema and display the names and counts of the tables where there is a disagreement." PL/SQL and SQL are both possible. I prefered PL/SQL way and tried to play around with nested tables and multiset operations. The procedure is shown below.

CREATE OR REPLACE PROCEDURE compare_table_counts(
   schema1_in IN VARCHAR2,
   schema2_in IN VARCHAR2,
   filter_in IN VARCHAR2)
IS

   TYPE type_aat IS TABLE OF VARCHAR2(100);
   tab_list_1 type_aat;
   tab_list_2 type_aat;
   tab_except type_aat;

   PROCEDURE make_tab_list(
      loc_tab IN OUT type_aat,schema_name IN VARCHAR2
   )
   IS
      sql_stmt VARCHAR2(200);
      loc_cnt PLS_INTEGER;
   BEGIN
      SELECT table_name
      BULK COLLECT INTO loc_tab
      FROM all_tables
      WHERE owner = schema_name
            AND table_name LIKE filter_in;

      FOR indx IN loc_tab.FIRST..loc_tab.LAST
      LOOP
         sql_stmt := 'SELECT COUNT(*) FROM '||
                      schema_name||'.'||
                      loc_tab(indx);
         EXECUTE IMMEDIATE sql_stmt INTO loc_cnt;
         loc_tab(indx) := loc_tab(indx) || ' (' ||
                                    loc_cnt || ')';
      END LOOP;
   END;

   PROCEDURE show_disagreement(
      schema_name IN VARCHAR2, loc_tab IN type_aat
   )
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('In schema '||schema_name||
                           ' differing tables...');
      DBMS_OUTPUT.PUT_LINE('(Row counts in parentheses)');
      FOR indx IN loc_tab.FIRST..loc_tab.LAST
      LOOP
         DBMS_OUTPUT.PUT_LINE(' ' || loc_tab(indx));
      END LOOP;
   END;

BEGIN
   make_tab_list(tab_list_1,schema1_in);
   make_tab_list(tab_list_2,schema2_in);

   tab_except := tab_list_1 MULTISET EXCEPT tab_list_2;
   IF tab_except.COUNT > 0 THEN
      show_disagreement(schema1_in,tab_except);
   END IF;

   tab_except := tab_list_2 MULTISET EXCEPT tab_list_1;
   IF tab_except.COUNT > 0 THEN
      show_disagreement(schema2_in,tab_except);
   END IF;

   EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
         DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);     
END;