PL/SQL create view base on dynamic table name

Question

I'm trying to create a view that access all the tables starting with the same name, they have the exactly same structure, and in time there will be more.

Table Names:

TEMP_ENTITIES_1000
TEMP_ENTITIES_1001
TEMP_ENTITIES_1002

and in the future there will be

TEMP_ENTITIES_1003
TEMP_ENTITIES_1004

and so on...

What I need is to use list of tables from the following script and then use the result the list of object name result to access in a view.

select object_name
from user_objects 
where object_type  = 'TABLE'
and object_name like upper('temp_entities_%');

create view entities_join as
select * from (object_name)

Is it possible to achieve?

Those table names, and the way you describe them and state that in the future there will be more, _VERY STRONGLY_ suggests a fatally flawed design. Why do you have multiple tables of the same design? Why isn't all this data in a single table, perhaps with an additional column to indicate whatever it was you are separating them on? Perhaps that extra column could be used as a partitioning key in a single partitioned table. — EdStevens, Sep 15 '21 at 14:35

score 2 · Answer 1 · answered Sep 15 '21 at 10:25

In modern versions of Oracle you may use SQL table macros without scheduled anything. It will build dynamic query on-the-fly and may be used as a plain view.

Below is the example:

SQL> insert all 
  2    when mod(rownum, 5) = 0 then into TEMP_ENTITIES_1000 values (l, dt, val) 
  3    when mod(rownum, 5) = 1 then into TEMP_ENTITIES_1001 values (l, dt, val) 
  4    when mod(rownum, 5) = 2 then into TEMP_ENTITIES_1002 values (l, dt, val) 
  5    when mod(rownum, 5) = 3 then into TEMP_ENTITIES_1003 values (l, dt, val) 
  6    when mod(rownum, 5) = 4 then into TEMP_ENTITIES_1004 values (l, dt, val) 
  7  
  8  select 
  9    level as l, 
 10    sysdate + level as dt, 
 11    level as val 
 12  from dual 
 13  connect by level < 10;

9 rows inserted.

SQL> 
SQL> create or replace function f_temp_entities_union 
  2  /*Create a macro*/
  3  return varchar2 SQL_MACRO
  4  is 
  5    v_union varchar2(4000); 
  6  begin 
  7    select listagg('select ''' || table_name || ''' as src, a.* from ' || table_name || ' a ', chr(10) || 'union all' || chr(10)) 
  8      into v_union 
  9    from all_tables 
 10    where table_name like 'TEMP_ENTITIES%' 
 11      and owner = sys_context('USERENV', 'CURRENT_SCHEMA') 
 12    ; 
 13  
 14    return v_union; 
 15  end;
 16  /

Function F_TEMP_ENTITIES_UNION compiled

SQL> 
SQL> select * 
  2  from f_temp_entities_union();
TEMP_ENTITIES_1000          5 20.09.21          5
TEMP_ENTITIES_1001          1 16.09.21          1
TEMP_ENTITIES_1001          6 21.09.21          6
TEMP_ENTITIES_1002          2 17.09.21          2
TEMP_ENTITIES_1002          7 22.09.21          7
TEMP_ENTITIES_1003          3 18.09.21          3
TEMP_ENTITIES_1003          8 23.09.21          8
TEMP_ENTITIES_1004          4 19.09.21          4
TEMP_ENTITIES_1004          9 24.09.21          9

9 rows selected. 

SQL> 
SQL> /*Add new table*/
SQL> create table TEMP_ENTITIES_1005 as select * from TEMP_ENTITIES_1001 where 1 = 0;

Table TEMP_ENTITIES_1005 created.

SQL> insert into TEMP_ENTITIES_1005
  2  select 1000 + rownum, sysdate + rownum, rownum - 100 from TEMP_ENTITIES_1000;

1 row inserted.

SQL> 
SQL> /*Check that new data is here*/
SQL> select *
  2  from v_demo;
TEMP_ENTITIES_1000          5 20.09.21          5
TEMP_ENTITIES_1001          1 16.09.21          1
TEMP_ENTITIES_1001          6 21.09.21          6
TEMP_ENTITIES_1002          2 17.09.21          2
TEMP_ENTITIES_1002          7 22.09.21          7
TEMP_ENTITIES_1003          3 18.09.21          3
TEMP_ENTITIES_1003          8 23.09.21          8
TEMP_ENTITIES_1004          4 19.09.21          4
TEMP_ENTITIES_1004          9 24.09.21          9
TEMP_ENTITIES_1005       1001 16.09.21        -99

10 rows selected.

Nice confrontation of the *old* and *new* world in two answers. The accepted answer should be definitively this *new perspective macro* approach — Marmite Bomber, Sep 15 '21 at 13:00

Littlefoot · Accepted Answer · 2021-09-15T08:44:34.907

Is it possible? Yes, using dynamic SQL.

However, from what you described, it looks that data model you use is wrong. You should have only one table with identifier that makes the difference (those 1000, 1001, ... values).

Then you wouldn't need a view at all, and do everything with that single table. I suggest you consider doing so.

Here's an example of what you might do (the way you asked):

Several sample tables:

SQL> select * from temp_entities_1000;

        ID NAME
---------- ------
      1000 Little

SQL> select * from temp_entities_1001;

        ID NAME
---------- ----
      1001 Foot

SQL> select * from temp_entities_1002;

        ID NAME
---------- -----
      1002 Scott

Procedure:

SQL> DECLARE
  2     l_str  VARCHAR2 (2000);
  3  BEGIN
  4     FOR cur_r IN (SELECT object_name
  5                     FROM user_objects
  6                    WHERE     object_type = 'TABLE'
  7                          AND object_name LIKE 'TEMP_ENTITIES%')
  8     LOOP
  9        l_str :=
 10           l_str || ' select * from ' || cur_r.object_name || ' union all ';
 11     END LOOP;
 12
 13     l_str :=
 14           'create or replace view entities_join as '
 15        || RTRIM (l_str, ' union all');
 16
 17     EXECUTE IMMEDIATE l_str;
 18  END;
 19  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from entities_join;

        ID NAME
---------- ------
      1000 Little
      1001 Foot
      1002 Scott

SQL>

You'd have to run that procedure every time new table is created so that it is included into the view.

PL/SQL create view base on dynamic table name

2 Answers2