This is an old revision of the document!

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