CODEWITHSUNDEEP
sqloracle-databaseunion
Jonjilla
Jonjilla

Reputation: 463

one query for many similar tables

I have an Oracle database with many tables that have identical structure (columns are all the same). The table names are similar also. The names of the tables are like table_1, table_2, table_3...

I know this isn't the most efficient design, but I don't have the option of changing this at this time.

In this case, is it possible to make a single sql query, to extract all rows with the same condition across multiple tables (hundreds of tables) without explicitly using the exact table name?

I realize I could use something like select * from table_1 UNION select * from table_2 UNION select * from table_3...select * from table_1000

But is there a more elegant sql statement that can be run that extracts from all matching table names into one result without having to name each table explicitly.

Something like

select * from table_%

Is something like that possible? If not, what is the most efficient way to write this query?

Upvotes: 2

Views: 1109

Answers (3)

Alex Poole
Alex Poole

Reputation: 191580

You can use dbms_xmlgen to query tables using a pattern, which generates an XML document as a CLOB:

select dbms_xmlgen.getxml('select * from ' || table_name
  || ' where some_col like ''%Test%''') as xml_clob
from user_tables
where table_name like 'TABLE_%';

You said you wanted a condition, so I've included a dummy one, where some_col like '%Test%'.

You can then use XMLTable to extract the values back as relational data, converting the CLOB to XMLType on the way:

select x.*
from (
  select xmltype(dbms_xmlgen.getxml('select * from ' || table_name
    || ' where some_col like ''%Test%''')) as xml
  from user_tables
  where table_name like 'TABLE_%'
) t
cross join xmltable('/ROWSET/ROW'
  passing t.xml
  columns id number path 'ID',
    some_col varchar2(10) path 'SOME_COL'
) x;

SQL Fiddle demo which retrieves one matching row from each of two similar tables. Of course, this assumes your table names follow a useful pattern like table_%, but you suggest they do.

This is the only way I know to do something like this without resorting to PL/SQL (and having searched back a bit, was probably inspired by this answer to count multiple tables). Whether it's efficient (enough) is something you'd need to test with your data.

Upvotes: 3

Mark Brackett
Mark Brackett

Reputation: 85685

This is kind of messy and best performed in a middle-tier, but I suppose you could basically loop over the tables and use EXECUTE IMMEDIATE to do it.

Something like:

for t in (select table_name from all_tables where table_name like 'table_%') loop
  execute immediate 'select blah from ' || t.table_name;
end loop;

Upvotes: 0

Frank Sun
Frank Sun

Reputation: 11

You can write "select * from table_1 and table_2 and tabl_3;"

Upvotes: -3

Related Questions