PLSQL - Create dynamic field foreach records using rules table columns

Question

I'm searching the best way (code-simplest and fastest method) to build a Stored Procedure (Oracle 11g) to insert all records (all fields) from a fact_table (source) into a target_table (destination), but it should create an additional field "dynamic_key" that will contain the values ​​of some columns, concatenated by pipe "|".

For each record, the information on "which columns to use to build the dynamic_key" is stored in a rule_table.

Example (assume all columns are VARCHAR2):

1. fact_table (tech_name, dim1, dim2, dim3, dim4, dim5, otherfields..) Ex. fact_table('foo', 'time', 'width' , 'height', 'volume', 'perimeter', otherfields.. )

2. rules_table (tech_name, filed1, field2, field3) Ex. rules_table('foo', dim2, dim4) The column tech_name is used to join (f.tecnical_name=r.tecnical_name) between fact_table and rules_table to get columns.

3. target_tables (dynamic_key, tecnical_name, dim1, dim2, dim3, dim4, dim5, otherfields..)

In this example code, the Stored-Procedure should:

INSERT INTO target_table(dynamic_key, tecnical_name, dim1, dim2, dim3, dim4, dim5, otherfields..) 
VALUES('width|volume', 'foo', 'time', 'width' , 'height', 'volume', 'perimeter', otherfields.. )

I think the best way is using a CURSOR and Dynamic SQL but it's curbersome to build dynamic_key and not very efficient: I have to process and insert one record at time.

Answer 1

You can achieve this by:

• enumerating all table columns with SELECT ... USER_TAB_COLS to create the static part of the INSERT
• iterating over the rules and building a large CASE expression for the dynamic key column
• using EXECUTE IMMEDIATE to run the generated statement

Complete example:

create table fact_table (tech_name varchar2(30), dim1 varchar2(30),
  dim2 varchar2(30), dim3 varchar2(30), dim4 varchar2(30),
  dim5 varchar2(30));

insert into fact_table values('foo', 'time', 'width' , 'height', 'volume', 'perimeter');
insert into fact_table values('bar', 'time', 'width' , 'height', 'volume', 'perimeter');
commit;

create table target_table as 
select t1.*, cast(null as varchar2(100)) as dynamic_key
from fact_table t1
where 1=0;  

create table rules_table(tech_name varchar2(30), field1 varchar2(30), field2 varchar2(30),
  field3 varchar2(30));

insert into rules_table values('foo', 'dim2', 'dim4', null);
insert into rules_table values('bar', 'dim1', null, null);
commit;

declare
  CRLF constant varchar2(10) := chr(13) ||chr(10);
  procedure insert_it is
      l_SQL varchar2(4000);
      l_columns varchar2(4000);
    begin
      -- get comma-separated list of columns present in FACT_TABLE
      select listagg(column_name, ',') within group(order by column_name)
      into l_columns
      from user_tab_cols
      where table_name = 'FACT_TABLE';
      -- build INSERT statement
      l_SQL := ' insert into target_table(' || l_columns || ', dynamic_key)' || CRLF ||
               '   select ' || l_columns || ',' || CRLF ||
               '      (case tech_name' || CRLF;
      -- build case branches from rules table
      for cur in (select * from rules_table order by tech_name)
        loop
          l_SQL := l_SQL || ' when ''' || cur.tech_name || ''' then ' ||
                     cur.field1 || (case when cur.field2 is not null then '|| ''|'' ||' || cur.field2 
                                         else null end)
                                || (case when cur.field3 is not null then '|| ''|'' ||' || cur.field3
                                         else null end) || CRLF;
        end loop;
        -- close case statement and add FROM clause
        l_SQL := l_SQL || 'end) from fact_table';                                            
        dbms_output.put_line(l_SQL);
        execute immediate l_SQL;                                      

    end;
begin
  insert_it;
end;