Storing SQL select as variable and diff later with another select

Question

I need to do the following.

1. Select count for all tables under a schema. I do this with:

select table_name,
  to_number(extractvalue(xmltype(
               dbms_xmlgen.getxml('select count(*) X from SCHEMA.'||table_name))
                  ,'/ROWSET/ROW/X')) as count
from all_tables tables where owner = 'SCHEMA' order by 1,2;

It gives two columns: a table name and current row count.

2. Store the results of this select in a variable.

3. Then insert some rows in several tables from an external interface.

4. Do the above select again.

5. And diff the results, showing only the tables that were affected.

I tried with the following, but it gives error:

"too many arguments"

declare name VARCHAR2(100);
begin
    select (
        select table_name,
        to_number(extractvalue(xmltype(
                dbms_xmlgen.getxml('select count(*) X from SCHEMA.'||table_name))
              ,'/ROWSET/ROW/X')) as count
        from all_tables tables where owner = 'SCHEMA'
    )
    into name
    from dual;
end;

Answer 1

You could use CTAS:

CREATE TABLE before_test
AS
select table_name,
to_number(extractvalue(xmltype(
          dbms_xmlgen.getxml('select count(*) X from SCHEMA.'||table_name))
          ,'/ROWSET/ROW/X')) as count
from all_tables tables where owner = 'SCHEMA';

Then do your data load and run once again but call it after_test.

When you got both tables then you could find difference using:

SELECT * FROM before_test MINUS SELECT * FROM after_test;

SELECT * FROM after_test  MINUS SELECT * FROM before_test;