SQL & PL/SQL :: How To Get All The Data From Child Tables

I have a requirement to get the data in "SQL" directly and should not use plsql/ can't redirect the output to a .sql file. I have a master table Main_tab which does have all "child table" names.

I want to build a query such that it will give all the data from child tables.

create table main_tab ( tab_name varchar(10));

insert into main_tab values ('one');
insert into main_tab values ('two');
insert into main_tab values ('three');
create table one ( a char);
insert into one values ('a')
create table two ( b char);
insert into two values ('b')
create table three ( c char);
insert into three values ('c');

Now I have got the query to combine all the table data using

select decode (rownum,1,'','union all ') || 'select * from ' || tab_name example from main_tab

but I am expecting the output to get as
a
b
c
which is the data from tables one,two and three respectively.

I don't want to save the above select data in to .sql and then execute it , instead I want to run it on "fly" in SQL itself. Is there such a possibility to do so?