Storing SQL select as variable and diff later with another select

Question

I need to do the following.

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.

Store the results of this select in a variable.
Then insert some rows in several tables from an external interface.
Do the above select again.
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;

Lukasz Szozda · Accepted Answer · 2018-10-05 13:10:42Z

0

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;

edited Oct 5, 2018 at 13:10

answered Oct 5, 2018 at 13:05

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SergioLeone Over a year ago

This looks very neat! Thanks!

Collectives™ on Stack Overflow

Storing SQL select as variable and diff later with another select

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related