Using dbms xmlgen as a means of dynamic SQL

select table_name
,      to_number( extractvalue( xmltype( dbms_xmlgen.getxml( 'select count(*) c from '||table_name) ),'/ROWSET/ROW/C') ) count
from   user_tables;

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5

A more complex example

SQL> create table mpi_memgc(x number, y varchar2(10), z varchar2(10))
Table created.
SQL> insert into mpi_memgc values (1,'bla','bli     ')
1 row created.
SQL> commit
Commit complete.

SQL> select 
  'select count(*) c from "'||table_name||'" where "'||column_name||
     '" like %  or "'||column_name||
     '" like  %' query,
 table_name,column_name,
 to_number(extractvalue(xmltype(dbms_xmlgen.getxml(
   'select count(*) c from "'||table_name||'" where "'||column_name||
     '" like %  or "'||column_name||
     '" like  %')),'/ROWSET/ROW/C')) count
from user_tab_columns
where table_name like 'MPI_MEMGC%' 
and table_name not like '%_H'
and data_type = 'VARCHAR2'
order by 1;

QUERY                                                                           
--------------------------------------------------------------------------------
TABLE_NAME                     COLUMN_NAME                         COUNT
------------------------------ ------------------------------ ----------
select count(*) c from "MPI_MEMGC" where "Y" like '% ' or "Y" like ' %'         
MPI_MEMGC                      Y                                       0

select count(*) c from "MPI_MEMGC" where "Z" like '% ' or "Z" like ' %'         
MPI_MEMGC                      Z                                       1