Query from multiple tables dynamically

Question

I want to query an object from DB that exists in any one of the tables. I am not sure about the table name that a particular object belongs to. For e.g. let's say my DB consists of various tables like Domestic_Passengers, Asian_Passengers, US_Passengers. And this table list may increase as well like in the future we may add the UK_Passengers table too.

So, I want to query something like

SELECT * FROM
(SELECT table_name FROM user_tables where table_name like '%PASSENGER')
WHERE NAME LIKE 'John%'

Is this possible?

I would strongly encourage you to use a single "Passengers" table with a "region" column instead. Then you can create "views" if you want to, for example, separate permissions by region. Querying tables without knowing their name can get messy and insecure. — D Stanley, Aug 09 '21 at 14:40
DB is already in use. I just gave some examples. It is a heavy database in production. Can't change. — Upendhar Singirikonda, Aug 09 '21 at 14:41
Create a view with `union` of all the tables and execute the query against it. Anyway you cannot do dynamic query with `select *`, because each table has its own structure. And this query will have no sense — astentx, Aug 09 '21 at 15:46

score 1 · Answer 1 · answered Aug 09 '21 at 17:23

That's a very bad database design.

I would suggest a view like this:

CREATE OR REPLACE VIEW PASSENGERS AS
SELECT * FROM Domestic_Passengers
UNION ALL
SELECT * FROM Asian_Passengers
UNION ALL
SELECT * FROM US_Passengers;

And then select from this view.

If this is not possible, then you need to run dynamic SQL in PL/SQL package. But this involves some code.

score 1 · Answer 2 · answered Aug 10 '21 at 04:38

The best answer depends on a lot of details, such if you can create database objects, how static are the tables, and how will this query be consumed.

If you can create schema objects, and the list of tables is somewhat stable, then Wernfried's answer of building a view is probably best.

If you can create schema objects, but the list of tables is very dynamic, and your application understands ref cursors, you should probably create a function that creates the SELECT and returns it through a ref cursor, like in this answer.

If you cannot create schema objects, then you're limited to the DBMS_XMLGEN/XMLTABLE trick. In a single query, build a string for the SELECT statement you want, run it through DBMS_XMLGEN to create an XMLType, and then use XMLTABLE to transform the XML back into rows and columns. This approach is slow and ugly, but it's the only way to have dynamic SQL in SQL without creating any custom PL/SQL objects. See my answer here for an example.

Query from multiple tables dynamically

2 Answers2