PL/SQL create view base on dynamic table name

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?

Solution

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.