set serveroutput on

DECLARE
    CURSOR fail_tables_cur IS
        SELECT table_name FROM user_tables WHERE table_name like '%_FAIL';
    vSQL    varchar2(200) := null;
BEGIN
    FOR rec IN fail_tables_cur
    LOOP
        vSQL := 'SELECT count(*) FROM ' || rec.table_name;
        dbms_output.put_line(vSQL);
         EXECUTE IMMEDIATE vSQL;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error: ' || sqlerrm);
END;

SELECT count(*) FROM MATITEMBADGES_FAIL
SELECT count(*) FROM RELPERSONFORENAMES_FAIL
SELECT count(*) FROM RELPERSONLOCATION_FAIL
SELECT count(*) FROM PERSONWARNINGSIGS_FAIL
SELECT count(*) FROM PERSONWARNSIGS_FAIL
SELECT count(*) FROM PERSON_FAIL
SELECT count(*) FROM PRECOURTEVENT_FAIL
SELECT count(*) FROM VHINTEL_FAIL
SELECT count(*) FROM XML_CUST_NOMINAL_FAIL
SELECT count(*) FROM RELPERSON_FAIL
SELECT count(*) FROM SEARCHPERSONINTIMATE_FAIL
SELECT count(*) FROM SEARCHPERSONPROP_FAIL
SELECT count(*) FROM VHTEXT_FAIL
SELECT count(*) FROM MATITEMCUSTODYSAMPLE_FAIL
SELECT count(*) FROM MATITEMCYCLES_FAIL
SELECT count(*) FROM MATITEMDRUGS_FAIL
SELECT count(*) FROM MATITEMMARKS_FAIL
SELECT count(*) FROM MATITEMPERSONPROP_FAIL
SELECT count(*) FROM MATITEMPROPERTY_FAIL
SELECT count(*) FROM MATITEMSAMPLEINTIMATE_FAIL
SELECT count(*) FROM MATITEMSOCO_FAIL
SELECT count(*) FROM CRIMEMATITEM_FAIL
SELECT count(*) FROM CRIMEPERSON_FAIL
SELECT count(*) FROM CRIME_FAIL
SELECT count(*) FROM CUSTODYCHARGES_FAIL
SELECT count(*) FROM CUSTODYPOLICEWORKER_FAIL
SELECT count(*) FROM CUSTODYRECREVIEW_FAIL
SELECT count(*) FROM CUSTODYREC_FAIL
SELECT count(*) FROM DETENTION_FAIL
SELECT count(*) FROM DISPOSAL_FAIL
SELECT count(*) FROM LIADDRESS_FAIL
SELECT count(*) FROM LIINTEL_FAIL
SELECT count(*) FROM LITEXT_FAIL
SELECT count(*) FROM MATITEMVEHICLE_FAIL
SELECT count(*) FROM NIADDRESS_FAIL
SELECT count(*) FROM NIBAIL_FAIL
SELECT count(*) FROM NICONVICTIONDETAILS_FAIL
SELECT count(*) FROM NICONVICTIONS_FAIL
SELECT count(*) FROM NIINTEL_FAIL
SELECT count(*) FROM NITEXT_FAIL
SELECT count(*) FROM NIWARRANT_FAIL
SELECT count(*) FROM OFFENCEDISPOSAL_FAIL
SELECT count(*) FROM OFFENCE_FAIL
SELECT count(*) FROM BAILCONDITIONS_FAIL
SELECT count(*) FROM BIADDRESS_FAIL
SELECT count(*) FROM BIINTEL_FAIL
SELECT count(*) FROM BITEXT_FAIL
SELECT count(*) FROM CASEPERSON_FAIL
SELECT count(*) FROM CASEPOLICEWORKER_FAIL
SELECT count(*) FROM PROCESS_FAIL
SELECT count(*) FROM OLD_PERSON_FAIL
SELECT count(*) FROM PERSONLOCATION_FAIL
PL/SQL procedure successfully completed.

EXECUTE IMMEDIATE vSQL INTO l_output_variable;

select table_name,
       to_number(
         extractvalue(
           dbms_xmlgen.getxmltype('select count(*) counter from ' ||
                                  u.table_name),
                      '/ROWSET/ROW/COUNTER')) counter
from user_tables

select table_name,
       to_number(extractvalue(
                   dbms_xmlgen.getXMLtype ('select count(*) cnt from '||table_name),
                   '/ROWSET/ROW/CNT')) rows_in_table
from user_tables
where ( tablespace_name is not null or partitioned='YES' )
  and table_name like '%_FAIL'
order by 1
/