PL SQL create view base on dynamic table name-CodePudding

I'm trying to create a view that access all the tables starting with de 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?

I'll wait for your reply, Thank you in advanced, Ana

CodePudding user response：

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.

CodePudding user response：

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.