Oracle - Performing operation on each row of result set

Question

I need help with a query. The query returns a column of all the views in the database. My ultimate goal is to have the whole result set be one column containing all the views in the database, and the other column containing how many records/rows are present in each corresponding table.

This:

SELECT DISTINCT OWNER, 
       OBJECT_NAME 
 FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'VIEW'
  AND OWNER = 'ADMIN'

returns the first column however I can't seem to find a way to combine it with :

select count(*) from view_X

to get the second column of the result set.

Any help would be appreciated. Thanks

Answer 1

With some XML magic, this can be done with a single statement:

select object_name as view_name,
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from "'||owner||'"."'||object_name||'"')),'/ROWSET/ROW/C')) as row_count
from dba_objects
where object_type = 'VIEW'
  and owner = 'ADMIN'
order by 1;