SQL & PL/SQL

Dear all,

you might have some ideas to help me out of my issue that i just "created myself" ;-)

i have a unknown and non-constant amount of tables using the the same table-structure and i do have a master table
that contains all names of these kind of tables. I now want to create a single view that contains all columns of each table
and an additional column name containing the name of the corresponding table.

I found a solution for this but only if I knew all table names while creating my view.

Here's what I currently have:

master_table:
ID TABLENAME
1 table_01
2 table_02


table_01:
ID NAME
1 eins
2 zwei
3 drei


table_02:
ID NAME
1 one
2 two
3 three


my view "tab1tab2" on these 2 table looks like this:

ID NAME TABLENAME
1 eins table_01
2 zwei table_01
3 drei table_01
1 one table_02
2 two table_02
3 three table_02


i achieved this view by using:

CREATE OR REPLACE VIEW TAB1TAB2 ("ID", "NAME", "TABLENAME")
AS
SELECT id,name, 'table_01' AS tablename FROM table_01
UNION
SELECT id,name, 'table_02' AS tablename FROM table_02;



Is there a way to create as many select and union statements as i do have entries (tablenames) in my master_table to achive the same results as my hardcoded view ?

Many thanks in advance for your help

Best regards

majo