How to dynamically query from multiple tables?

Question

Let's suppose the following:

• Schema 1 has multiple tables: table1_a, table2, table3, etc
• Schema 2 has one table that contains the name of schema1 tables, so: table1_b with column1 that contains the values table1_a, table2, table3, etc..

The base query is as follows:

SELECT *
FROM "schema1"."table1"

But instead of having "table1", I want to have multiple tables from that schema without specifying their name, so basically I want to get the table names from schema2 and access the columns of the tables with that name in schema1 without hardcoding them.

In a more "python-esque code" way, it would be something like:

x = [table_name] in schema2.table1_b.column1

SELECT max(column2)
FROM "schema1".x

I want to avoid using PL/SQL if it's possible.

Answer 1

There are several ways to run dynamic SQL in SQL. If the final query always returns the same number and types of columns, the DBMS_XMLGEN trick is powerful enough.

For example, imagine a huge number of tables that all store the time each row was inserted. The time is stored as Unix time, the number of seconds since January 1, 1970. The application has been doing this for decades, and not all of the column names are consistent. How can you quickly find the latest time all tables were updated?

(This is not a contrived example. In the real world, schemas get weird and complicated, even when you follow all the best practices.)

First, create the tables with different column names and values:

--drop table log1;
--drop table log2;
--drop table log3;
--drop table control_table;

create table log1(id number, unix_time number);
create table log2(id number, epoch number);
create table log3(id number, unix_epoch number);

insert into log1 values(1, 1700000000);
insert into log2 values(2, 1800000000);
insert into log3 values(2, 1900000000);

Next, create a table to contain the relevant table and column names:

create table control_table(table_name varchar2(128), column_name varchar2(128));
insert into control_table values('LOG1', 'UNIX_TIME');
insert into control_table values('LOG2', 'EPOCH');
insert into control_table values('LOG3', 'UNIX_EPOCH');
commit;

This query dynamically finds the maximum value for each table. It's a complex query, but on the plus side it does not require any additional privileges or PL/SQL code:

select
    table_name,
    extractvalue
    (
        dbms_xmlgen.getxmltype('select max('||column_name||') unix_time from ' || table_name)
        , '/ROWSET/ROW/UNIX_TIME'
    ) last_timestamp
from control_table
order by table_name;

TABLE_NAME    LAST_TIMESTAMP
----------    --------------
LOG1          1700000000
LOG2          1800000000
LOG3          1900000000

The query gets more complicated if column and table names are quoted identifiers that require double quotes. Or if the values are stored as dates, which require extra date formatting with XML. And there is potentially a SQL injection issue, if you can't trust your table and column names.

The above code only works if the query returns the same columns for each table. If you want a solution where the number and types of columns is dynamic, that can also be done, but it requires more work and some predefined PL/SQL objects. Anything can be done in dynamic SQL, but the complexity depends on precisely what is dynamic.