title: PLSQL - Create dynamic field foreach records using rules table columns
body: <p>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 "|". </p>
  
  <p>For each record, the information on <strong>"which columns to use to build the dynamic_key"</strong> is stored in a rule_table.</p>
  
  <p>Example (assume all columns are VARCHAR2):</p>
  
  <ol>
  <li><p>fact_table (tech_name, dim1, <strong>dim2</strong>, dim3, <strong>dim4</strong>, dim5, otherfields..)
  Ex. fact_table('foo', 'time', 'width' , 'height', 'volume',
  'perimeter', otherfields.. )</p></li>
  <li><p>rules_table (tech_name, filed1, field2, field3) Ex.
  rules_table('foo', <strong>dim2</strong>, <strong>dim4</strong>) The column tech_name is used to join
  (f.tecnical_name=r.tecnical_name) between fact_table and rules_table
  to get columns.</p></li>
  <li><p>target_tables (dynamic_key, tecnical_name, dim1, dim2, dim3, dim4,
  dim5,  otherfields..)</p></li>
  </ol>
  
  <p>In this example code, the Stored-Procedure should:</p>
  
  <pre><code>INSERT INTO target_table(dynamic_key, tecnical_name, dim1, dim2, dim3, dim4, dim5, otherfields..) 
  VALUES(<b>'width|volume'</b>, 'foo', 'time', 'width' , 'height', 'volume', 'perimeter', otherfields.. )</code></pre>
  
  <p>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.</p>