Is it possible to use column values to determine the tablename in a from clause of a subselect?

Question

I would like to run a query that uses a column value (in which a tablename is stored) to determine the tablename in a from clause of a subselect.

Something like that:

SELECT column_with_tablename,

(SELECT COUNT(*) FROM VALUEOF(column_with_tablename)) as numberofitems

FROM table1

I know that this is very fragile but i need it work. (I inherited a database that stores tablenames in a column)

Is there a way ?

You can do this in PL/SQL with an `execute` statement. You cannot do it directly in a single query. — Gordon Linoff, Jun 25 '15 at 11:58
I can use plsql, but i am not sure if i can create a view using plsql?! — Lucian Depold, Jun 25 '15 at 12:43

score 2 · Accepted Answer · edited May 23 '17 at 12:22

2

for a pure sql solution, refer to this answer

select column_with_tablename,
       to_number(extractvalue(xmltype
         (dbms_xmlgen.getxml('select count(*) c from '||column_with_tablename)
         ),'/ROWSET/ROW/C')) as count
from table1

edited May 23 '17 at 12:22

Community

1
1

answered Jun 25 '15 at 14:14

HAL 9000

3,877
1
23
29

Is it possible to use column values to determine the tablename in a from clause of a subselect?

1 Answers1