Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle - Performing operation on each row of result set

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

like image 554
user1964821 Avatar asked Mar 22 '23 05:03

user1964821


1 Answers

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;
like image 114
a_horse_with_no_name Avatar answered Apr 02 '23 02:04

a_horse_with_no_name