Variable Table name in Oracle SQL using dbms_xmlgen

Requirement

To find count of all tables

The interesting stuff in oracle is not just writing queries that preforms the required task but to write some single liners that could do the same work as lengthy procedures

so another requirement is to make it interesting

Solution

Solution 1

A procedural way to achieve this through looping for cursor is
DECLARE
CURSOR c1 IS
SELECT table_name
FROM   user_tables;
tname      VARCHAR2(200);
count_stmt VARCHAR2(5000);
count1     NUMBER;
count2     NUMBER;
BEGIN
OPEN c1;

FETCH c1 INTO tname;

WHILE c1%found LOOP
count_stmt := 'select count(1) from '
|| tname;

EXECUTE IMMEDIATE count_stmt INTO count1;

count_stmt := tname;

dbms_output.Put(count_stmt);

count2 := 30 - Length(tname);

WHILE count2 > 0 LOOP
dbms_output.Put(' ');

count2 := count2 - 1;
END LOOP;

dbms_output.Put_line(count1);

FETCH c1 INTO tname;
END LOOP;
END;

set serveroutput on to see the result

Solution 2

I was struggling to find some way to run dynamic sql query inside the select phrase something like

select table_name, (select count(*) from var_table_name) from user_tables

where var_table_name is variable that should hold value of table_name for each row processed from user_tables

and I found this

SELECT table_name,
To_number (Extractvalue (Xmltype (
dbms_xmlgen.Getxml('select count(*) c from '
||table_name)),
'/ROWSET/ROW/C')) AS COUNT
FROM   user_tables; 

dbms_xmlgen.getxml : Converts the results from the SQL query string to XML format, and returns the XML as a temporary CLOB
extractvalue  : gets the child value from the node.XML result from oracle is in the form