Switching between multiple tables at runtime in Oracle

Question

I am working in an environment where we have separate tables for each client (this is something which I can't change due to security and other requirements). For example, if we have clients ACME and MEGAMART then we'd have an ACME_INFO table and MEGAMART_INFO tables and both tables would have the same structure (let's say ID, SOMEVAL1, SOMEVAL2).

I would like to have a way to easily access the different tables dynamically.

To this point I've dealt with this in a few ways including:

1. Using dynamic SQL in procedures/functions (not fun)
2. Creating a view which does a UNION ALL on all of the tables and which adds a CLIENT_ID COLUMN (i.e. "CREATE VIEW COMBINED_VIEW AS SELECT 'ACME' CLIENT_ID, ID, SOMEVAL1, SOMEVAL2 FROM ACME_INFO UNION ALL SELECT 'MEGMART' CLIENT_ID, ID, SOMEVAL1, SOMEVAL2") which performs surprisingly well, but is a pain to maintain and kind of defeats some of the requirements which dictate that we have separate tables for each client.
3. SYNONYMs won't work because we need different connections to act on different clients
4. A view which refers to a package which has a package variable for the active client. This is just evil and doesn't even work out all that well.

What I'd really like is to be able to create a table function, macro, or something else where I can do something like

SELECT * FROM FN_CLIENT_INFO('ACME');

or even

UPDATE (SELECT * FROM FN_CLIENT_INFO('ACME')) SET SOMEVAL1 = 444 WHERE ID = 3;

I know that I can partially achieve this with a pipelined function, but this mechanism will need to be used by a reporting platform and if the reporting platform does something like

SELECT * FROM FN_CLIENT_INFO('ACME') WHERE SOMEVAL1 = 4 

then I want it to run efficiently (assuming SOMEVAL1 has an index for example). This is where a macro would do well.

Macros seem like a good solution, but the above won't work due to protections put in place to prevent against SQL injection.

Is there a way to create a macro that somehow verifies that the passed in VARCHAR2 is a valid table name and therefore can be used or is there some other approach to address what I need?

I was thinking that if I had a function which could translate a client name to a DBMS_TF.TABLE_T then I could use a macro, but I haven't found a way to do that well.

Answer 1

A lesser-known method for such cases is to use a system-partitioned table. For instance, consider the following code:

Full example: https://dbfiddle.uk/UQsAgHCk

create table t_common(a int, b int)
  partition by system (
     partition ACME_INFO,
     partition MEGAMART_INFO
  );

insert into t_common partition(acme_info)
  values(1,1);
insert into t_common partition(megamart_info)
  values(2,2);
commit;

select * from t_common partition(acme_info);
select * from t_common partition(megamart_info);

As demonstrated, a common table can be used with different partitions for different clients, allowing it to be used as a regular table. We can create a system-partitioned table and utilize the exchange partition feature with older tables. Then, we can drop the older tables and create views with the same names, so that older code continues to work with views while all new code can work with the common table by specifying a partition.