SQL & PL/SQL :: Dynamic Insert From Multiple Tables?

We get data from our customers which we load into temporary tables.The goal is to consolidate this data into one single table.

Following are the rules:

1) final table should have all the columns from all the tables. If there are common column(s) then add only one column with that name.

2) the join would be based on all the common columns

3) if there is a common row, we merge the row into one (example, the row with DOMAIN = ACME.COM)

4) There could be 'N' number of tables

Following is the most realistic data.

1) T1/T2/T3 has the sample data which cover most of our test cases

2) We are expected to transform the data from T1/T2/T3 as depicted in table T4.

3) we might have more than 3 tables in our production environment, so the query should work for N tables.

4) I have given the explanation of how each row should be derived to be inserted in T4

5) the only information we have to work with is the TABLE_NAME(s) and its metadata from USER_TAB_COLUMNS

DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
DROP TABLE T4;

[code].....

Explanation for each row:

row1) This row comes from T1 and T2 (not T3 because HOSTNAME would not match)
row2) This row comes from T1 and T3 (not T2 because HOSTNAME would not match)
row3) This row comes from T1 and T3
row4) This row comes from T2 and T3
row5) This row comes from T3