title: Oracle Database 10g: collect, cast for string aggregation
body: <p>I'm trying to use <code>collect</code> and <code>cast</code> for string aggregation, similar to <code>listagg</code> in Oracle Database 11 (also, unfortunately, <code>wm_concat</code> is not enabled on the database I'm querying). I also have read-only access to this database.</p>
  
  <p>My current query is as follows</p>
  
  <pre><code>    SELECT plan.EMPLID,
          plan.STRM TERM,
          CAST(COLLECT(plan.ACAD_PLAN) AS sys.dbms_debug_vc2coll) agg_plans
      FROM SYSADM.PS_OSR_PLAN_TRM plan
      WHERE plan.EMPLID = '999999999'
      GROUP BY plan.EMPLID, plan.STRM
  </code></pre>
  
  <p>This gives me this result:</p>
  
  <pre><code>EMPLID      TERM    AGG_PLANS
  999999999   1152    SYS.DBMS_DEBUG_VC2COLL(VARCHAR(ECON-MN, TXTLCLO-MN))
  999999999   1154    SYS.DBMS_DEBUG_VC2COLL(VARCHAR(ACCTING-BS, ECON-MN))
  </code></pre>
  
  <p>I believe my logic is correct, but I'm having with trouble with the <code>cast</code> statement. I know that <code>sys.dbms_debug_vc2coll</code> is of type <code>table of varchar2(1000)</code>. I would like to remove <code>SYS.DBMS_DEBUG_VC2COLL(VARCHAR(</code> from each row and simply list the results.</p>
  
  <p>Is there a way to join together the resulting collection into a string? This stack overflow <a href="https://stackoverflow.com/questions/16407560/why-do-i-get-ora-00932-inconsistent-datatypes-expected-got-when-using-co">post</a> has something similar to what I'm looking for... however, again, I have readonly access to this database. I'd also like to try to keep it one select statement.</p>
  
  <p>Thanks for any help.</p>
  
  <p>EDIT: I am not opposed to a solution that uses <code>xmlagg</code>.</p>