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

I know it is giving the total count of row of each table . But what to know how it working ?

CodePudding user response：

Examining the query starting from innermost part is best way to understand:

'select count(*) c from ' || table_name

creates a string containing query selecting the number of records from the table referred to by table_name, so if table_name contains xyz the query will be select count(*) from xyz.

xmltype(dbms_xmlgen.getxml(<query>))

executes the dynamically generated query, producing an XML result.

to_number(extractvalue(<xml>, '/ROWSET/ROW/C'))

fetches a specific value from the XML generated before, following a specific path. We need to assume the XML looks like<ROWSET><ROW><C>value</C></ROW></ROWSET>. The extracted value, yet a string, is then converted to a number.

select table_name, <number> count from user_tables order by table_name

is what finally remains...

CodePudding user response：

Break it down:

• 'select count(*) c from '||table_name gives you a string containing a query against a specific table (from user_tables);
• dbms_xmlgen.getxml('select count(*) c from '||table_name) uses the dbms_xmlgen package to run that dynamic query string and returns the result as an XML document, but as a CLOB;
• xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)) converts that CLOB to actual XML;
• extractvalue(..., '/ROWSET/ROW/C') extracts the C node value from that XML (there is only one per document, and there is one document per table), as a string;
• to_number(...) just converts that string to a number.

db<>fiddle with three dummy tables, showing the intermediate steps.

However, the version you have seems to have originated with Laurent Schneider in 2007, and things have moved on a bit since then. The extractvalue() function is deprecated so you should use XMLQuery instead, and you can skip a step by using getxmltype() instead of xmltype(getxml()):

select table_name,
  to_number(
    xmlquery(
      '/ROWSET/ROW/C/text()'
      passing dbms_xmlgen.getxmltype('select count(*) c from '||table_name)
      returning content
    )
  ) count
from user_tables
order by table_name;

Or (as @Padders mentioned) you could use XMLTable, with a CTE or inline view to provide the XML; which perhaps makes this example a bit more obscure, but is useful if you have more than one value to extract:

select t.table_name, x.count
from (
  select table_name,
    dbms_xmlgen.getxmltype('select count(*) c from '||table_name) as xml
  from user_tables
) t
cross apply xmltable (
  '/ROWSET/ROW'
  passing t.xml
  columns count number path 'C'
) x
order by table_name;

The principal is the same though, and I've included those versions in an expanded db<>fiddle.

(Incidentally, I'm not advocating using a keyword like count as a column alias - it's better to avoid those; I'm just sticking with the alais from your original query.)