How to perform a dynamic select query for multiple tables in oracle? using table name and column names as values from other table?

Question

i have a table having columns such schema name,table name,column name.

schema name	table name	column name
dbo	product	colour
dbo	sales	quantity
dbo	customer	order

i want to perform an action such that to get a count of records based on column as column name and table as table name

select count(colour) as count from dbo.product
select count(quantity) as count from dbo.sales
select count(order) as count from dbo.customer

can u suggest me correct steps to achieve this using Oracle database. thanks in advance

expected output

count
5
50
150

Answer 1

If you need to get count per column per table, you can do this with plain SQL using DBMS_XMLGEN package, which essentially executes new cursors dynamically. I think you can adapt the example query below to suit your needs (aggregate counts or convert them to another format).

with a as (
  select
    'all_tables' as table_name, 'table_name' as column_name
  from dual
  union all
  select 'all_tables', 'tablespace_name' from dual union all
  select 'all_tab_cols', 'column_name' from dual union all
  select 'all_indexes', 'index_name' from dual union all
  select 'all_indexes', 'tablespace_name' from dual
)
select
  table_name,
  column_name,
  cast(extractvalue(
    dbms_xmlgen.getxmltype(
      'select count(' || column_name || ') as cnt' || chr(10) ||
      'from ' || table_name
    ),
    '/ROWSET/ROW/CNT'
  ) as int) as cnt
from a

TABLE_NAME   | COLUMN_NAME     |   CNT
:----------- | :-------------- | ----:
all_tables   | table_name      |    71
all_tables   | tablespace_name |    43
all_tab_cols | column_name     | 20983
all_indexes  | index_name      |    81
all_indexes  | tablespace_name |    73

db<>fiddle here

Answer 2

You cannot do this in pure sql, but you can pretty easily do this in pl/sql. In the code below I'm storing the results in a table called rowcounts and it assumes your table with tables and column names is called

CREATE table mytables (schema_name VARCHAR2(100), table_name, VARCHAR2(100), column_name VARCHAR2(100));
INSERT INTO mytables (schema_name, table_name, column_name) VALUES ('dbo','product','colour');
INSERT INTO mytables (schema_name, table_name, column_name) VALUES ('dbo','sales','quantity');
INSERT INTO mytables (schema_name, table_name, column_name) VALUES ('dbo','customer','order');

CREATE TABLE rowcounts (table_name VARCHAR2(500), rowcount NUMBER);

DECLARE 
  l_rowcount INT;
BEGIN
  FOR r IN (SELECT * FROM mytables) LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT('||r.column_name||') FROM '||r.table_name INTO l_rowcount;
    --dbms_output.put_line('SELECT COUNT('||r.column_name||') FROM '||r.table_name);
    INSERT INTO rowcounts(table_name, rowcount) VALUES (r.table_name, l_rowcount);
  END LOOP;
  COMMIT;
END;
/

--DROP TABLE rowcounts;