Multi tables with same schema, query design strategy?

Question

I have an Oracle database with a dodgy design: many tables have same schema. They're pieces from a huge table, and time by time, new tables will be created with specific rows. Of course, I'm not authorized to access the huge table, only the small ones. How can I query against these tables, effectively?

Many thanks in advance.

Answer 1

AFAIK, there's no way to use a single prepared statement against multiple distinct tables, if that's what you mean. Maybe you could make a view that's a union of all the tables...?

Answer 2

You can create a pipelined function that dynamically builds a SQL statement to select from all the relevant tables.

--Create tables.  They can be created at any time, but they have
--to follow some sort of naming convention
create table temp1(test1 number, test2 varchar2(100));
create table temp2(test1 number, test2 varchar2(100));
create table temp3(test1 number, test2 varchar2(100));


--Create a package that will return one result at a time from all the tables.
create or replace package query_tables is
  type output_rectype is record
  (
    test1 number
    ,test2 varchar2(100)
  );
  type output_rectype_table is table of output_rectype;
  function query return output_rectype_table pipelined;
end query_tables;
/


create or replace package body query_tables is
  function query return output_rectype_table pipelined is
    sql_statement varchar2(32767);
    output output_rectype;
    my_cursor sys_refcursor;
  begin
    --Build a select statment that combines all relevant tables
    for tables in (select table_name from user_tables where table_name like 'TEMP%') loop
      sql_statement := sql_statement || 'select * from '||tables.table_name||' union all ';
    end loop;
    --Remove the last 'union all'
    sql_statement := substr(sql_statement, 1, length(sql_statement) - 11);

    --Get one result at a time and return it
    open my_cursor for sql_statement;
    loop
      fetch my_cursor into output;
      exit when my_cursor%notfound;
      pipe row(output);
    end loop;
  end;
end query_tables;
/


--Insert some test data
insert into temp1 values(1, 'asdf');
insert into temp2 values(2, 'fgds');
insert into temp3 values(3, 'rewq');


--Display the results from all tables with one query
select * from table(query_tables.query);