dynamic table name in select statement

Question

I have a series of history tables in an oracle 9 database. History_table_00 contains last months data, History_table_01 contains the month before, and History_table_02 the month before that. Next month, History_table_02 will automatically get renamed to history_table_03, history_table_01 renamed to history_table_02, history_table_00 renamed to history_table_01, and a new history_table_00 will be created to gather the newest history (I really hope I am making sense).

Anyway, I need to write a select statement that will dynamically select all history tables. I am hoping this won't be too complicated because they all share the same name, just appended with sequential number so I can discover the table names with:

select table_name from all_tables where table_name like 'HISTORY_TABLE_%';

My standard query for each table is going to be:

select id, name, data_column_1, data_column_2 from history_table_%;

What do I have to do to accomplish the goal of writing a sql statement that will always select from all history tables without me needing to go in every month and add the new table? Thanks for anything you guys can provide.

Solution 1

I would suggest you to define a view in which you select from all history tables using union all and each time the tables are renamed you modify the view as well.

create OR replace view history_data as 
SELECT id, name, data_column_1, data_column_2  FROM history_table_01
union all 
SELECT id, name, data_column_1, data_column_2  FROM history_table_02
union all 
SELECT id, name, data_column_1, data_column_2  FROM history_table_03
;

then you can simle SELECT * FROM history_data;

you can build the view dynamicaly with the help of the following statment:

SELECT 'SELECT id, name, data_column_1, data_column_2  FROM ' || table_name || ' union all ' 
FROM  user_tables 
WHERE table_name like 'HISTORY_TABLE_%'     

Solution 2

you can use ref cursor but i wouldn't recommend it. it goes like this

create table tab_01 as select 1 a , 10 b from dual;
create table tab_02 as select 2 a , 20 b from dual;
create table tab_03 as select 3 a , 30 b from dual;

create or replace function get_all_history
return sys_refcursor
as
   r sys_refcursor;
   stmt varchar2(32000);
   cursor c_tables is
           select  table_name
           from    user_tables
           where   table_name like 'TAB_%';
begin
   for x in c_tables loop
           stmt := stmt || ' select * from ' || x.table_name ||' union all';
   end loop;
   stmt := substr(stmt , 1 , length(stmt) - length('union all'));
   open r for stmt;
   return r;
end;
/

SQL> select get_all_history() from dual;

GET_ALL_HISTORY()
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

         A          B
---------- ----------
         1         10
         2         20
         3         30

Comments

[user1588433]

thanks @SeanPrice, don't know what my solution is yet. I'll give yours a whirl and see.

[user1588433]

thanks @schurik. But, having to go in manually every month an modify a view is exactly what I am trying to avoid.

[schurik]

how do you rename/create the tables?, you can integrate the create veiw statment in that process.

[user1588433]

that's a good suggestion. I'll bring that up with the guy who owns the process and see what he can do.