Search code examples
oracle-databaseoracle12cpartitioning

In Oracle I want to create a "routing interface" which insert into separate tables based on parameter

I need to find a solution to the following problem: there should be a common and single "interface" that I can use in an insert into statement, something like this: insert into INTERFACE (fields) select ... But there are many tables with the same structure behind the interface which should decide based on list of values (coming in a field) where to put the data. The tables are partitioned by range interval (daily) right now.

I was thinking about having a composite partitioned table which cannot be SELECT-ed to avoid mixing different type of data in a single select query, but creating views on the top of it. In this case the table should be partitioned like this: partition by list FIELD subpartition by range interval. But oracle 12 does not support this.

Any idea how to solve this? (There is a reason why I need a single interface and why I have to store data separately.)

Thank you in advance!

Solution

  • The INSERT ALL syntax can help easily route data to specific tables based on conditions:

    create table interface1(a number, b number);
create table interface2(a number, b number);

insert all
    when a <= 1 then
        into interface1
    else
        into interface2
select '1' a, 2 b from dual;