Dynamic Table Name In Select Statement

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 be

Solution 1:

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

createtable tab_01 asselect1 a , 10 b from dual;
createtable tab_02 asselect2 a , 20 b from dual;
createtable tab_03 asselect3 a , 30 b from dual;

createor replace function get_all_history
return sys_refcursor
as
   r sys_refcursor;
   stmt varchar2(32000);
   cursor c_tables isselect  table_name
           from    user_tables
           where   table_name like'TAB_%';
beginfor 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 : 1CURSOR STATEMENT : 1

         A          B
---------- ----------110220330

Solution 2:

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.

createOR replace view history_data asSELECT id, name, data_column_1, data_column_2  FROM history_table_01
unionallSELECT id, name, data_column_1, data_column_2  FROM history_table_02
unionallSELECT 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 3:

The best idea is to do a dynamic SQL statement that builds up a large query for each table existing in the database. Give the following SQL query try. (please forgive my formatting, I am not sure how to do line-breaks on here)

DECLARE@tableVARCHAR(255)
      , @objectIDINT
      , @selectQueryVARCHAR(MAX)

SELECT@objectID=MIN(object_id)
  FROM sys.tables
 WHERE name LIKE'history_table_%'

WHILE @objectIDISNOTNULLBEGINSELECT@table= name
    FROM sys.tables
   WHERE object_id =@objectIDORDERBY object_id

  SELECT@selectQuery= ISNULL(@selectQuery+' UNION ALL ', '') +'select id, name, data_column_1, data_column_2 FROM '+@tableSELECT@objectID=MIN(object_id)
    FROM sys.tables
   WHERE name LIKE'tblt%'AND object_id >@objectIDENDSELECT@selectQuery--EXEC (@selectQuery)

Solution 4:

A Possible Answer :

CREATE OR REPLACE PROCEDURE GET_HIST_DETAILS IS

DECLARE

QUERY_STATEMENT VARCHAR2(4000) := NULL;
CNT             NUMBER;

BEGIN 

selectCOUNT(table_name) INTO CNT from all_tables where table_name like 'HISTORY_TABLE_%';

FOR loop_counter IN 1..CNT

LOOP

 IF LOOP_COUNTER <> CNT THEN
 {  
 QUERY_STATEMENT := QUERY_STATEMENT || 'select id, name, data_column_1, data_column_2 from history_table_0' || loop_counter || ' UNION';
 }
 ELSE
 {
   QUERY_STATEMENT := QUERY_STATEMENT || 'select id, name, data_column_1, data_column_2 from history_table_0' || loop_counter ;
 } 

EXECUTE_IMMEDIATE QUERY_STATEMENT;

END LOOP;


END GET_DETAILS;

PS:I dont have Oracle installed , so havent tested it for syntax errors.