CODEWITHSUNDEEP
oracle-database
jumaks pogi
jumaks pogi

Reputation: 1

How to merge two or more unknown tables into one table in Oracle

I'm trying to merge x number of identical tables into one table. The reason we did this is because we want to have, for example 50 columns per table in the database. Tables are created externally via SCADA software called Ignition.

Every time table is created in the database, we want to view the data as one regardless of how many tables the data came from provided that all tables will have the same first three letters for example, Table_1, Table_2, Table_3....so on.

The query/procedure we want to have is like: step 1: since the tables are unknown we can't do it by simple union, merge insert etc., so we must find all table_name with 'Table' prefix.

SELECT table_name FROM all_tables where table_name like 'Table%'

step 2: this is where the magic begins, it should query one by one each listed table_name in first step, then collect all the data and merge into one table or view.

I tried many ways using PL/SQL but don't know how to proceed with step 2. Is there any way to get what we want to achieve? any possible solutions would be great! :)

Thanks!

Upvotes: 0

Views: 176

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

Assuming that you are selecting only the common columns from all the tables, you could create a dynamic view, which does a UNION ALL of all the tables starting with "Table" prefix.

DECLARE
     v_select   CLOB;
BEGIN
     SELECT
          LISTAGG('SELECT col1,col2,col3 FROM ' || table_name,
                  ' UNION ALL ' || CHR(10) ) WITHIN GROUP
          (
               ORDER BY table_name
          )
     INTO v_select
     FROM user_tables WHERE table_name LIKE 'TABLE_%';
     IF
          v_select IS NOT NULL
     THEN
          EXECUTE IMMEDIATE ' CREATE OR REPLACE VIEW v_all_tabs as ' || v_select;
     END IF;
END;
/

Then select from the view by executing the above block(or put it into a procedure ) each time there's a new table added.

select * from v_all_tabs;

If there's a chance of your SQL string exceeding 4000 characters, instead of a single LISTAGG, you could append each select through a simple assignment in PL/SQL in a cursor loop.

Upvotes: 2

Related Questions