title: Concatenating collections in PLSQL
body: <p>I need to collect a lot of ids from a couple of different tables into a variable of some sort to be passed to another function. Which tables to take the ids from is dynamic, depending on the param iVar below. The question is if there is no better way to do this as this approach will have to copy and re allocate the arrays multiple times. Would it be better to insert it all in a temp table? Would it be better to use dynamic sql. See the get_ids function below:</p>
  
  <pre><code>FUNCTION concat (
      iList1 IN ID_ARRAY,
  iList2 IN ID_ARRAY
  ) 
  RETURN ID_ARRAY IS
      lConcat ID_ARRAY;
  BEGIN
      SELECT column_value BULK COLLECT INTO lConcat FROM (
          (SELECT column_value FROM TABLE(CAST( iList1 AS ID_ARRAY))) 
          UNION ALL 
          (SELECT column_value FROM TABLE(CAST( iList2 AS ID_ARRAY)))
      );
      RETURN lConcat;
  END concat;
  
  FUNCTION get_ids (
      iVar           IN NUMBER
  ) 
  RETURN ID_ID_ARRAY IS
      lIds ID_ARRAY;
  BEGIN
      lids := get_ids0();
      IF iVar = 1 THEN
          lIds := concat(lFilter, get_ids1());
      ELSE
          lIds := concat(lFilter, get_ids3());
          IF iVar = 4 THEN
              lIds := concat(lFilter, get_ids4());
          END IF;
      END IF;
      RETURN lIds;
  END get_ids;
  </code></pre>